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。