问题描述
我有一张这样的桌子:
id START_DATE end_date
1 01/01/2011 01/10/2011
2 01/11/2011 01/20/2011
3 01/25/2011 02/01/2011
4 02/10/2011 02/15/2011
5 02/16/2011 02/27/2011
我想合并 start_date 是另一条记录的 end_date 的第二天的记录:所以结束记录应该是这样的:
I want to merge the records where the start_date is just next day of end_date of another record: So the end record should be something like this:
new_id START_DATE end_date
1 01/01/2011 01/20/2011
2 01/25/2011 02/01/2011
3 02/10/2011 02/27/2011
我知道的一种方法是创建一个基于行的临时表,其中包含各种行作为日期(一个日期的每条记录,在总天数范围内),从而使表变平.
One way that I know to do this will be to create a row based temp table with various rows as dates (each record for one date, between the total range of days) and thus making the table flat.
但是必须有一种更简洁的方法在单个查询中执行此操作...例如使用 row_num 的东西?
But there has to be a cleaner way to do this in a single query... e.g. something using row_num?
谢谢各位.
推荐答案
declare @T table
(
id int,
start_date datetime,
end_date datetime
)
insert into @T values
(1, '01/01/2011', '01/10/2011'),
(2, '01/11/2011', '01/20/2011'),
(3, '01/25/2011', '02/01/2011'),
(4, '02/10/2011', '02/15/2011'),
(5, '02/16/2011', '02/27/2011')
select row_number() over(order by min(dt)) as new_id,
min(dt) as start_date,
max(dt) as end_date
from (
select dateadd(day, N.Number, start_date) as dt,
dateadd(day, N.Number - row_number() over(order by dateadd(day, N.Number, start_date)), start_date) as grp
from @T
inner join master..spt_values as N
on N.number between 0 and datediff(day, start_date, end_date) and
N.type = 'P'
) as T
group by grp
order by new_id
您可以使用 数字表 而不是使用 master..spt_values
.
You can use a numbers table instead of using master..spt_values
.
这篇关于查询合并连续时间记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!