查询合并连续时间记录 [英] Query to merge continuous temporal records

问题描述

我有一张这样的桌子:

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.

这篇关于查询合并连续时间记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！