首页 新闻 赞助 找找看

Sql server 2008 对表字段 统计增量的问题

0
[已解决问题] 解决于 2018-09-03 10:54

表结构及数据如上,请使用Sql语句,统计出每辆汽车每天行驶的里程数。

 

汽车编号(CarID) 总里程(Mileage) 年(M_Year) 月(M_Month) 日(M_Day)
1 10 2015 1 1
1 15 2015 1 2
1 15 2015 1 5
1 20 2015 1 6
1 25 2015 1 7
1 27 2015 1 8
1 30 2015 1 9
1 30 2015 1 10
1 32 2015 1 11
2 20 2015 1 10
2 40 2015 1 11
2 50 2015 1 12
3 14 2015 1 1
3 18 2015 1 10
3 31 2015 1 20

 

 

统计结果:

汽车编号(CarID) 里程(Mileage) 年(M_Year) 月(M_Month) 日(M_Day)
1 10 2015 1 1
1 5 2015 1 2
1 0 2015 1 5
1 5 2015 1 6

 

 

Sql server  2012 中可以用 LAG 函数,参考如下文章。。

http://www.cnblogs.com/bluedoctor/p/4901259.html

 

 

表结构及测试数据脚本:

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)

 

liushou007的主页 liushou007 | 菜鸟二级 | 园豆:206
提问于:2015-10-22 15:57
< >
分享
最佳答案
0
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 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;
liushou007 | 菜鸟二级 |园豆:206 | 2015-10-22 16:30
其他回答(1)
0

你要问什么,如果用Sql语句,统计出每辆汽车每天行驶的里程数,你就需要更加年月日分组就行了

稳稳的河 | 园豆:4216 (老鸟四级) | 2015-10-22 16:09
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册