以下是我在sql Server 2008数据库中拥有的表的子集.
我试图输出序列号,具有最大连续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
解决方法
看看下面的例子
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
