SQLSERVER 2012计算上一条,下一条数据的函数-阿里云开发者社区

开发者社区> 开发与运维> 正文
登录阅读全文

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]
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,如需转载请自行联系原作者



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
开发与运维
使用钉钉扫一扫加入圈子
+ 订阅

集结各类场景实战经验,助你开发运维畅行无忧

其他文章
最新文章
相关文章