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)
最佳答案
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;
其他回答(1)
0
你要问什么，如果用Sql语句，统计出每辆汽车每天行驶的里程数，你就需要更加年月日分组就行了
