计算里程差及累积里程

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)
go
/****** Script for SelectTopNRows command from SSMS  ******/
with cte
as
(
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_Day
from cte a
go