如何在SQL中选择重叠的日期范围 [英] How to select overlapping date ranges in SQL

问题描述

我有一个包含以下列的表格: sID，开始日期和结束日期

I have a table with the following columns : sID, start_date and end_date

其中一些值如下:

1   1995-07-28  2003-07-20 
1   2003-07-21  2010-05-04 
1   2010-05-03  2010-05-03 
2   1960-01-01  2011-03-01 
2   2011-03-02  2012-03-13 
2   2012-03-12  2012-10-21 
2   2012-10-22  2012-11-08 
3   2003-07-23  2010-05-02

我只希望结果中的第二行和第三行是重叠的日期范围.

I only want the 2nd and 3rd rows in my result as they are the overlapping date ranges.

我尝试过此方法，但它不会摆脱第一行.不确定我要去哪里错了吗?

I tried this but it would not get rid of the first row. Not sure where I am going wrong?

select a.sID from table a
inner join table b 
on a.sID = b.sID
and ((b.start_date between a.start_date and a.end_date)
and (b.end_date between a.start_date and b.end_date ))
order by end_date desc

我正在尝试在SQL Server中做

I am trying to do in SQL Server

推荐答案

合理有效地做到这一点的一种方法是

One way of doing this reasonably efficiently is

WITH T1
     AS (SELECT *,
                MAX(end_date) OVER (PARTITION BY sID ORDER BY start_date) AS max_end_date_so_far
         FROM   YourTable),
     T2
     AS (SELECT *,
                range_start = IIF(start_date <= LAG(max_end_date_so_far) OVER (PARTITION BY sID ORDER BY start_date), 0, 1),
                next_range_start = IIF(LEAD(start_date) OVER (PARTITION BY sID ORDER BY start_date) <= max_end_date_so_far, 0, 1)
         FROM   T1)
SELECT SId,
       start_date,
       end_date
FROM   T2
WHERE  0 IN ( range_start, next_range_start ); 

如果您在(sID, start_date) INCLUDE (end_date)上有索引，则可以执行一次有序扫描.

if you have an index on (sID, start_date) INCLUDE (end_date) this can perform the work with a single ordered scan.

这篇关于如何在SQL中选择重叠的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！