专注收集记录技术开发学习笔记、技术难点、解决方案
网站信息搜索 >> 请输入关键词:
您当前的位置: 首页 > Sql Server

计算里程差及积累里程

发布时间:2010-05-20 14:01:29 文章来源:www.iduyao.cn 采编人员:星星草
计算里程差及累积里程
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

 

友情提示:
信息收集于互联网,如果您发现错误或造成侵权,请及时通知本站更正或删除,具体联系方式见页面底部联系我们,谢谢。

其他相似内容:

热门推荐: