SQL Server的发现overlaping日期范围(SQL Server find overla

我有一个日期范围（季节）每年的表。正常的是，一个赛季结束就是下个赛季的beggining。在下面的例子中我大胆的两个不规则的季节设置。在季节1的第一端部是季节2的beggining在第二后一天，季节4的beggining为一天seaon 3的端部后

+--------+----+--------------+--------------+
|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:

这个回答你的问题的一半：使用重叠的日期查询从这篇文章中找到冲突的记录：

-- 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小提琴

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中可以使用窗口功能得到改善（只比较相邻赛季）。

Answer 4:

DECLARE @T TABLE (
    SeasonId    INT     NOT NULL,
    Year        INT     NOT NULL,
    DateFrom    DATE    NOT NULL,
    DateTo      DATE    NOT NULL
)

INSERT @T VALUES (1, 14, '2014-01-01', '2014-01-31')
INSERT @T VALUES (2, 14, '2014-01-30', '2014-03-01')
INSERT @T VALUES (3, 14, '2014-03-01', '2014-05-22')
INSERT @T VALUES (4, 14, '2014-05-23', '2014-10-16')
INSERT @T VALUES (5, 14, '2014-10-16', '2014-12-01')

;WITH T AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY DateFrom) RowId
    FROM @T
)
SELECT
    A.SeasonId, A.DateTo,
    B.SeasonId, B.DateFrom
FROM T A JOIN T B ON A.RowId + 1 = B.RowId
WHERE A.DateTo <> B.DateFrom

文章来源: SQL Server find overlaping date ranges