实际需求很普遍，比如求销售数据的每天与头一天的销售增长量。这里用一个汽车行驶数据来做例子：
先初始化数据：
CREATE TABLE [dbo].[CarData]( [CarID] [int] NULL, [Mileage] [int] NULL, [M_year] [int] NULL, [M_Month] [int] NULL, [M_Day] [int] NULL ) ON [PRIMARY] GO INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 10, 2015, 1, 1) INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 15, 2015, 1, 2) INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 15, 2015, 1, 5) INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 20, 2015, 1, 6) INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 26, 2015, 1, 9) INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 30, 2015, 1, 10) INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 35, 2015, 1, 11) INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (2, 20, 2015, 1, 5) INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (2, 22, 2015, 1, 8) INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (2, 40, 2015, 1, 10) INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (2, 45, 2015, 1, 11) INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (3, 50, 2015, 1, 11)
然后，使用下面的SQL来统计：
WITH ONE AS( SELECT ROW_NUMBER() OVER(PARTITION BY CarId ORDER BY CarId, M_Year, M_Month, M_Day) AS NodeId ,C.CarId ,C.Mileage ,C.M_Year ,C.M_Month ,C.M_Day FROM dbo.CarData AS C ) SELECT * ,COALESCE(One.Mileage - LAG(One.Mileage) over(PARTITION BY CarId order by One.NodeId),0) AS '增量' FROM ONE
这里使用LAG函数来计算。
注意，这个查询只有在SQLSERVER 2012以上才支持，2008不支持，所以采用下面的方法实现：
WITH TWO AS( SELECT ROW_NUMBER() OVER(PARTITION BY CarId ORDER BY CarId, M_Year, M_Month, M_Day) AS NodeId ,C.CarId ,C.Mileage ,C.M_Year ,C.M_Month ,C.M_Day FROM [dbo].[CarData] AS C ) SELECT A.* , A.Mileage - COALESCE(B.NextMileage, 0) AS '增量' FROM TWO AS A OUTER APPLY (SELECT Mileage AS NextMileage FROM TWO AS B WHERE B.NodeId = A.NodeId - 1 AND B.CarId = A.CarId ) AS B;
执行查询，将得到下面的结果：
1 1 10 2015 1 1 10 2 1 15 2015 1 2 5 3 1 15 2015 1 5 0 4 1 20 2015 1 6 5 5 1 26 2015 1 9 6 6 1 30 2015 1 10 4 7 1 35 2015 1 11 5 1 2 20 2015 1 5 20 2 2 22 2015 1 8 2 3 2 40 2015 1 10 18 4 2 45 2015 1 11 5 1 3 50 2015 1 11 50
感谢 SOD开发技术群（PWMIS开发框架-SOD会员群 43109929）朋友提供的程序。
本文转自深蓝医生博客园博客，原文链接：http://www.cnblogs.com/bluedoctor/p/4901259.html，如需转载请自行联系原作者
