SQL 选择 : how to return count of consecutive values from an ordered data set

Question

以下是我在 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。

在此先感谢您提供的任何帮助!

此致，

副总裁

Answer 1

看看下面的例子

SQL Fiddle Demo

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