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

我所期待的是,这个群体是依次返回的,不止是一位职业介绍所。

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。

您可在<代码>中使用这一数值。单位:。

本条在我的博客中详细介绍了它如何运作:

Grouping continuous ranges

Answer 2

第一项评论——采用表格变量而不是排位表,是更好的做法。那么,你就能够使用这样的trick。 • 确保你在正确秩序中加入价值观(即:终止最后结果):

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

这是我所猜测的《工作法典》每变化一次的第一次产出。

友情链接