问题描述
我有一个问题,我需要从按列分组的表中获取最早的日期值,但按顺序分组.
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:
- 对连续范围进行分组