SQL选择：如何从有序数据集中返回连续值的计数
以下是我在SQL Server 2008数据库中的一个表的子集。
我想输出的Serial Number ，该LID与连续的最大数LID值，而实际计数。 重要的是要注意，该表按“ Last Updated Date值降序排列（此条件非常重要）。 它可以按Serial Number分组或按Serial Number升序或降序排序......无论哪种方法更有效率和合理。
以下是数据的样子：
[Serial Number] [LID] [Last Updated Date]
--------------------------------------
123456 AAA 2012-09-24
123456 AAA 2012-09-23
123456 AAA 2012-09-22
123456 AAA 2012-09-21
123456 BBB 2012-09-20
123456 BBB 2012-09-19
123456 AAA 2012-09-18
123456 AAA 2012-09-17
123456 AAA 2012-09-16
234567 BBB 2012-09-24
234567 BBB 2012-09-23
234567 AAA 2012-09-22
表中所需的输出是：
[Serial Number] [LID] [LID Count]
-------------------------------------------
123456 AAA 4
234567 BBB 2
我很茫然。 我试过使用
ROW_NUMBER() OVER(PARTITION BY [Service Tag], [LID]
ORDER BY [Last Updated Date] DESC)
但是所有这些都是按照日期顺序来分解的，最后我会计算日期范围内发生次数最多的计数和LID。
预先感谢您提供的任何帮助！
最好的祝福，
VP
看看下面的例子
SQL小提琴演示
DECLARE @Table TABLE(
[Serial Number] INT,
[LID] VARCHAR(50),
[Last Updated Date] DATETIME
)
INSERT INTO @Table SELECT 123456,'AAA','2012-09-24'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-23'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-22'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-21'
INSERT INTO @Table SELECT 123456,'BBB','2012-09-20'
INSERT INTO @Table SELECT 123456,'BBB','2012-09-19'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-18'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-17'
INSERT INTO @Table SELECT 123456,'AAA','2012-09-16'
INSERT INTO @Table SELECT 234567,'BBB','2012-09-24'
INSERT INTO @Table SELECT 234567,'BBB','2012-09-23'
INSERT INTO @Table SELECT 234567,'AAA','2012-09-22'
;WITH Vals AS (
SELECT *,
ROW_NUMBER() OVER(ORDER BY [Serial Number],[Last Updated Date] DESC) ROWID
FROM @Table
)
, ValsNext AS (
SELECT v.[Serial Number],
v.LID,
v.[Last Updated Date],
v.ROWID,
MIN(vn.ROWID) NextRowID
FROM Vals v LEFT JOIN
Vals vN ON v.[Serial Number] = vn.[Serial Number]
AND v.LID != vn.LID
AND v.ROWID < vn.ROWID
GROUP BY v.[Serial Number],
v.LID,
v.[Last Updated Date],
v.ROWID
)
, ValDiffs AS (
SELECT vn.[Serial Number],
vn.LID,
vn. NextRowID - vn.ROWID Consecutive
FROM ValsNext vn
)
, Serials AS (
SELECT [Serial Number],
MAX(Consecutive) MaxConsecutive
FROM ValDiffs
GROUP BY [Serial Number]
)
SELECT vd.*
FROM Serials s INNER JOIN
ValDiffs vd ON s.[Serial Number] = vd.[Serial Number]
AND s.MaxConsecutive = vd.Consecutive
这是一个解决方案
;with cte as (
select sn, lid, ludate,
datediff(d,row_number() over (
partition by sn,lid
order by ludate),ludate) dd
from xtable
), cte2 as (
select sn,lid,dd,count(*)c,
rn=row_number() over (partition by lid
order by count(*) desc)
from cte
group by sn,lid,dd
)
select sn [Serial Number], lid, c [Count]
from cte2
where rn=1
第一个CTE使用复杂的DATEADD表达式将连续的日期组合在一起，使用列dd标记。
第二个CTE是计数的常规行编号。
上一篇: SQL Select: how to return count of consecutive values from an ordered data set