你的位置:首页 > 数据库

[数据库]SQLSERVER 2012计算上一条,下一条数据的函数


实际需求很普遍,比如求销售数据的每天与头一天的销售增长量。这里用一个汽车行驶数据来做例子:

先初始化数据:

CREATE TABLE [dbo].[CarData](  [CarID] [int] NULL,  [Mileage] [int] NULL,  [M_year] [int] NULL,  [M_Month] [int] NULL,  [M_Day] [int] NULL) ON [PRIMARY]GOINSERT [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不支持。

感谢 SOD开发技术群(PWMIS开发框架-SOD会员群 43109929)朋友提供的程序。