你的位置:首页 > 数据库

[数据库]计算里程差及累积里程


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)go/****** Script for SelectTopNRows command from SSMS ******/with cteas(SELECT [CarID]   ,[Mileage]   ,[M_year]   ,[M_Month]   ,[M_Day]   ,ROW_NUMBER() over (PARTITION by carid order by m_month,m_day) as 分组内序号 FROM [test].[dbo].[CarData])--计算里程增量及累积里程select a.CarID ,a.Mileage ,增量=    COALESCE    (      (        select a.mileage-b.Mileage from cte b        where a.CarID=b.CarID and a.分组内序号-b.分组内序号 =1      ),0    ),累积里程= (select sum(b.Mileage) from cte as b where a.CarID=b.CarID and a.分组内序号>=b.分组内序号),a.M_year ,a.M_Month ,a.M_Dayfrom cte ago