SQL查询，根据列值的变化找到最早的日期

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

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

Here is a sample table:

这是一个示例表:

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]

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

这个查询将返回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

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

我要查找的是，组是连续的，并返回多个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

Is this possible without a cursor?

如果没有光标，这是可能的吗?

2 个解决方案

#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

Continuous ranges have same difference between partitioned and unpartitioned ROW_NUMBERs.

连续范围在分区和未分区的ROW_NUMBERs之间有相同的差异。

You can use this value in the GROUP BY.

可以在GROUP BY中使用这个值。

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

请在我的博客中查看这篇文章，了解它是如何工作的:

Grouping continuous ranges
分组连续范围

#2

First comment - using a table variable not a temp table would be better practice. Then you can use a trick like this. Make sure you insert the values in the right order (i.e. ascending LastEffectiveDate):

第一个注释——使用表变量而不是临时表将是更好的实践。然后你可以用这样的技巧。确保以正确的顺序插入值(即升序LastEffectiveDate):

DECLARE @tmp table
(
    Sequence            INT IDENTITY,
    UserID              BIGINT,
    JobCodeID           BIGINT,
    LastEffectiveDate   DATETIME
)

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 TOP 1 JobCodeID, LastEffectiveDate
FROM @tmp

UNION ALL

SELECT t2.JobCodeID, t2.LastEffectiveDate
FROM @tmp t1
    INNER JOIN
        @tmp t2
        ON t1.Sequence + 1 = t2.Sequence
WHERE t1.JobCodeID <> t2.JobCodeID

This outputs the first date each time the job code changes, which I am guessing is what you want from your description.

这将在每次工作代码更改时输出第一个日期，我猜这是您希望从您的描述中得到的。

#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