在SQL中查找日期范围重叠的记录 [英] Find records with overlapping date range in SQL
本文介绍了在SQL中查找日期范围重叠的记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下表格和数据:
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');
我需要一个SELECT
查询来查找具有重叠日期的记录.这意味着在上面的示例中,我应该在结果中包含四个记录
I need a SELECT
query to find the records with overlapping dates. It means that in the example above, I should have four records in result
number
2
3
7
8
先谢谢您. 我正在使用Oracle DB.
Thank you in advance. I am using Oracle DB.
推荐答案
尝试一下:
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);
这篇关于在SQL中查找日期范围重叠的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文