oracle 在 SQL 中查找日期范围重叠的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29943714/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find records with overlapping date range in SQL
提问by Lukasz
I have following table and data:
我有下表和数据：
CREATE TABLE customer_wer(
id_customer NUMBER,
name VARCHAR2(10),
surname VARCHAR2(20),
date_from DATE,
date_to DATE NOT NULL,
CONSTRAINT customer_wer_pk PRIMARY KEY (id_customer, data_from));
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-JAN-00', '31-MAR-00');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-APR-00', '30-JUN-00');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '15-JUN-00', '30-SEP-00');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-OCT-00', '31-DEC-00');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-JAN-01', '31-MAR-01');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-APR-01', '30-JUN-01');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-JUL-01', '5-OCT-01');
INSERT INTO customer_wer VALUES (4, 'Karolina', 'Komuda', '01-OCT-01', '31-DEC-01');
I need a SELECTquery to find the records with overlapping dates. It means that in the example above, I should have four records in result
我需要一个SELECT查询来查找日期重叠的记录。这意味着在上面的例子中，我应该在结果中有四个记录
number
2
3
7
8
Thank you in advance. I am using Oracle DB.
先感谢您。我正在使用 Oracle 数据库。
采纳答案by Giorgi Nakeuri
Try this:
尝试这个：
select * from t t1
join t t2 on (t1.datefrom > t2.datefrom and t1.datefrom < t2.dateto)
or (t1.dateto > t2.datefrom and t1.dateto < t2.dateto)
Thank You for this example. After modification it is working:
谢谢你的这个例子。修改后运行正常：
SELECT *
FROM customer_wer k
JOIN customer_wer w
ON k.id_customer = w.id_customer
WHERE (k.date_from > w.date_to AND k.date_from < w.date_to)
OR (k.date_to > w.date_from AND k.date_to < w.date_to);
回答by Ben
the earlier answer does not account for situations where t2 is entirely within t1
较早的答案没有考虑 t2 完全在 t1 内的情况
select * from t t1
join t t2 on (t1.datefrom > t2.datefrom and t1.datefrom < t2.dateto )
or (t1.dateto > t2.datefrom and t1.dateto < t2.dateto )
or (t1.dateto > t2.dateto and t1.datefrom < t2.datefrom)
回答by Guest
If each beginning date is less than or equal to the other record's ending date, then it will be within the range.
如果每个开始日期都小于或等于其他记录的结束日期，则它将在该范围内。
If starting date 1 is less than ending date 2 AND starting date 2 is less than ending date 1.
如果开始日期 1 小于结束日期 2 并且开始日期 2 小于结束日期 1。
SELECT *
FROM t t1
JOIN t t2 ON (t1.datefrom <= t2.dateto)
AND (t2.datefrom <= t1.dateto)
回答by Artistan
Similar to the answer by @Ben ... but check for overlapping == days as well with a check on id to ensure it is unique.
与@Ben 的回答类似......但检查重叠 == 天数以及检查 id 以确保它是唯一的。
select * from seasons t1
join seasons t2 on (t1.season_open >= t2.season_open and t1.season_open <= t2.season_close and t1.id != t2.id)
or (t1.season_close >= t2.season_open and t1.season_close <= t2.season_close and t1.id != t2.id)
or (t1.season_close >= t2.season_close and t1.season_open <= t2.season_open and t1.id != t2.id)
