SQL选择：如何从有序数据集返回连续值的计数

我试图输出序列号,具有最大连续LID值的LID和实际计数.重要的是要注意,表按最后更新日期值降序排序(此条件很关键).它可以按序列号分组,也可以按序列号升序或降序排序……更高效,更有意义.

这是数据的样子：

[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 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,'2012-09-23'
INSERT INTO @Table SELECT 123456,'2012-09-22'
INSERT INTO @Table SELECT 123456,'2012-09-21'
INSERT INTO @Table SELECT 123456,'BBB','2012-09-20'
INSERT INTO @Table SELECT 123456,'2012-09-19'
INSERT INTO @Table SELECT 123456,'2012-09-18'
INSERT INTO @Table SELECT 123456,'2012-09-17'
INSERT INTO @Table SELECT 123456,'2012-09-16'
INSERT INTO @Table SELECT 234567,'2012-09-24'
INSERT INTO @Table SELECT 234567,'2012-09-23'
INSERT INTO @Table SELECT 234567,'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.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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!