SQL查询查找依赖于列值更改的最早日期

Question

我有一个问题，我需要从按列分组的表中获取最早的日期值，但按顺序分组。

这是一个示例表:

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 行，带有最小值。

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，如下所示:

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

没有光标这可能吗？

Answer 1

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 之间具有相同的差异。

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

有关其工作原理的更多详细信息，请参阅我博客中的这篇文章:

• Grouping continuous ranges