T-SQL查询组按日期顺序(空白和孤岛) [英] T-SQL query group in date order (gaps and islands)
本文介绍了T-SQL查询组按日期顺序(空白和孤岛)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个这样的示例表:
I have a sample table like this:
CREATE TABLE #Aggregate
(
vKey INT
,dKey INT
,StartTrip DATETIME
,EndTrip DATETIME
,Distance INT
)
具有一些示例数据
INSERT INTO #Aggregate
(vKey, dKey, StartTrip, EndTrip, Distance )
VALUES
(4940, 0, '2016-09-14 09:05:47.000', '2016-09-14 10:07:45.000', 25)
,(4940, 0, '2016-09-15 14:09:40.000', '2016-09-15 14:11:33.000', 35)
,(4940, 1202, '2016-09-16 17:07:04.000', '2016-09-16 18:07:04.000', 61)
,(4940, 0, '2016-09-26 16:43:03.000', '2016-09-26 16:44:52.000', 0)
,(4940, 0, '2016-09-28 11:13:41.000', '2016-09-28 11:14:33.000', 5)
,(4940, 1202, '2016-10-01 13:41:03.000', '2016-10-01 14:02:39.000', 500)
,(4940, 1202, '2016-10-01 21:52:14.000', '2016-10-01 21:54:28.000', 5)
,(4940, 0, '2016-10-01 10:27:44.000', '2016-10-01 10:36:24.000', 75)
我需要按日期顺序和vKey/DKey组合对数据进行分组,并像这样显示
I need to group the data in date order and in vKey/DKey combinations and present like so
vKey dKey StartTrip EndTrip Distance
4940 0 14/09/2016 09:05:47 15/09/2016 14:11:33 60
4940 1202 16/09/2016 17:07:04 16/09/2016 18:07:04 61
4940 0 26/09/2016 16:43:03 28/09/2016 11:14:33 5
4940 1202 01/10/2016 13:41:03 01/10/2016 21:54:28 505
4940 0 01/10/2016 10:27:44 01/10/2016 10:36:24 75
最好的方法是什么?
预先感谢
推荐答案
Select vKey
,dKey
,StartTrip = min(StartTrip)
,EndTrip = max(EndTrip)
,Distance = sum(Distance)
From (
Select *
,Island = Row_Number() over (Partition By vKey Order by Month(StartTrip)) - Row_Number() over (Partition By vKey,dKey Order by StartTrip)
From #Aggrgate
) A
Group By Island,vKey,dKey
Order By min(StartTrip)
返回
这篇关于T-SQL查询组按日期顺序(空白和孤岛)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文