問題描述

我有一個問題，我需要從按列分組的表中獲取最早的日期值，但按順序分組.

I have a problem where I need to get the earliest date value from a table grouped by a column, but sequentially grouped.

這是一個示例表:

if object_id('tempdb..#tmp') is NOT null 
    DROP TABLE #tmp

CREATE TABLE #tmp
(
    UserID              BIGINT      NOT NULL,
    JobCodeID           BIGINT      NOT NULL,
    LastEffectiveDate   DATETIME    NOT NULL
)

INSERT INTO #tmp VALUES ( 1, 5, '1/1/2010') 
INSERT INTO #tmp VALUES ( 1, 5, '1/2/2010') 
INSERT INTO #tmp VALUES ( 1, 6, '1/3/2010') 
INSERT INTO #tmp VALUES ( 1, 5, '1/4/2010') 
INSERT INTO #tmp VALUES ( 1, 1, '1/5/2010') 
INSERT INTO #tmp VALUES ( 1, 1, '1/6/2010')

SELECT JobCodeID, MIN(LastEffectiveDate)
FROM #tmp
WHERE UserID = 1
GROUP BY JobCodeID

DROP TABLE [#tmp]

此查詢將返回 3 行，其中包含最小值.

This query will return 3 rows, with the min value.

1   2010-01-05 00:00:00.000
5   2010-01-01 00:00:00.000
6   2010-01-03 00:00:00.000

我正在尋找的是該組是連續的并返回多個 JobCodeID，如下所示:

What I am looking for is for the group to be sequential and return more than one JobCodeID, like this:

5   2010-01-01 00:00:00.000
6   2010-01-03 00:00:00.000
5   2010-01-04 00:00:00.000
1   2010-01-05 00:00:00.000

這可以不用游標嗎?

推薦答案

SELECT  JobCodeId, MIN(LastEffectiveDate) AS mindate
FROM    (
        SELECT  *,
                prn - rn AS diff
        FROM    (
                SELECT  *,
                        ROW_NUMBER() OVER (PARTITION BY JobCodeID 
                                    ORDER BY LastEffectiveDate) AS prn,
                        ROW_NUMBER() OVER (ORDER BY LastEffectiveDate) AS rn
                FROM    @tmp
                ) q
        ) q2
GROUP BY
        JobCodeId, diff
ORDER BY
        mindate

連續范圍在分區和未分區ROW_NUMBERs之間具有相同的差異.

Continuous ranges have same difference between partitioned and unpartitioned ROW_NUMBERs.

您可以在 GROUP BY 中使用此值.

You can use this value in the GROUP BY.

有關其工作原理的更多詳細信息，請參閱我博客中的這篇文章:

See this article in my blog for more detail on how it works:

• 對連續范圍進行分組

這篇關于SQL查詢根據列值變化查找最早日期的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！