我有一个包含以下列的表:
sID、开始日期和结束日期
一些值如下:
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
我只想要结果中的第二行和第三行,因为它们是重叠的日期范围。
我尝试了这个,但它不会摆脱第一行。不确定我哪里出错了?
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 中执行此操作
合理有效地做到这一点的一种方法是
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)
这可以通过单个有序扫描来执行工作。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)