SQL Server查找重叠日期范围

Question

我有一个表，其中包含每年的日期范围(季节)。正常的情况是，一个赛季的结束是下一个赛季的乞讨。在下面的例子中，我用粗体显示了两个不规则的赛季设置。第一季的结束是第二季乞讨结束后的第二天，第二季的乞讨是第三季结束后的一天。

+--------+----+--------------+--------------+
|SEASONID|YEAR|DATE FROM     |DATE TO       |
+--------+----+--------------+--------------+
|1       |14  |  2014-01-01  |**2014-01-31**|
|2       |14  |**2014-01-30**|  2014-03-01  |
|3       |14  |  2014-03-01  |**2014-05-22**|
|4       |14  |**2014-05-23**|  2014-10-16  |
|5       |14  |  2014-10-16  |  2014-12-01  |
+--------+----+--------------+--------------+

有没有一种方法可以编写一个查询来捕获没有正确设置的季节？(一季的结束不是下一季的开始)

Answer 1

这回答了您问题的一半:使用this article中的重叠日期查询来查找冲突的记录：

-- 1.2) select date ranges that overlap [d1, d2) (d2 and end_date are exclusive)
-- SELECT * FROM <table> WHERE @d2 > start_date AND end_date > @d1

SELECT s1.*
FROM seasons AS s1
INNER JOIN seasons AS s2 ON s1.seasonid <> s2.seasonid
AND s2.date_to > s1.date_from
AND s1.date_to > s2.date_from

结果：

+--------+----+----------+----------+--------+----+----------+----------+
|seasonid|year|date_from |date_to   |seasonid|year|date_from |date_to   |
+--------+----+----------+----------+--------+----+----------+----------+
|1       |14  |2014-01-01|2014-01-31|2       |14  |2014-01-30|2014-03-01|
+--------+----+----------+----------+--------+----+----------+----------+
|2       |14  |2014-01-30|2014-03-01|1       |14  |2014-01-01|2014-01-31|
+--------+----+----------+----------+--------+----+----------+----------+

SQL Fiddle

Answer 2

我希望这能对你有所帮助

select * from stest st
join stest st1
on st.edate ! = st1.sdate 
where st1.id = st.id + 1

其中stest具有以下详细信息

+--+----+----------+----------+
|id|year|sdate     |edate     |
+--+----+----------+----------+
|1 |14  |2014-01-01|2014-01-31|
+--+----+----------+----------+
|2 |14  |2014-01-30|2014-03-01|
+--+----+----------+----------+
|3 |14  |2014-03-01|2014-05-22|
+--+----+----------+----------+
|4 |14  |2014-05-23|2014-10-16|
+--+----+----------+----------+
|5 |14  |2014-10-16|2014-12-01|
+--+----+----------+----------+

上面的查询将给出以下结果

+--+----+----------+----------+--+----+----------+----------+
|id|year|sdate     |edate     |id|year|sdate     |edate     |
+--+----+----------+----------+--+----+----------+----------+
|1 |14  |2014-01-01|2014-01-31|2 |14  |2014-01-30|2014-03-01|
+--+----+----------+----------+--+----+----------+----------+
|3 |14  |2014-03-01|2014-05-22|4 |14  |2014-05-23|2014-10-16|
+--+----+----------+----------+--+----+----------+----------+

从结果可以得到id ( 1,2 )和(3,4)不匹配的

如果你已经像下面这样做了

select * from stest st
join stest st1
on st.edate  = st1.sdate 
where st1.id = st.id + 1

然后你就会得到匹配的结果

+--+----+----------+----------+--+----+----------+----------+
|id|year|sdate     |edateid   |id|year|sdate     |edate     |
+--+----+----------+----------+--+----+----------+----------+
|2 |14  |2014-01-30|2014-03-01|3 |14  |2014-03-01|2014-05-22|
+--+----+----------+----------+--+----+----------+----------+
|4 |14  |2014-05-23|2014-10-16|5 |14  |2014-10-16|2014-12-01|
+--+----+----------+----------+--+----+----------+----------+

Answer 3

一种特别的暴力方法：

SELECT *
FROM seasons s1
CROSS JOIN seasons s2
WHERE NOT (s1.date_to <= s2.date_from OR s1.date_from >= s2.date_to);

希望没有足够的记录来引起笛卡尔连接的性能问题。如果您使用的是SQL Server 2012，则可以使用窗口函数进行改进(仅比较相邻的季节)。