在SQL中查找日期范围重叠的记录

Question

我有以下表格和数据：

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查询来查找日期重叠的记录。这意味着在上面的示例中，我的结果应该有四条记录。

number 
2
3
7
8

预先感谢你。我正在使用Oracle数据库。

- Lukasz

---

你正在使用哪种数据库管理系统？ - Rahul Tripathi

正如@RahulTripathi所建议的那样，您必须告诉我们您正在使用哪个DB，可能还要提供版本号。这是Windowing Functions或Temporal tables非常有用的内容，但并不是所有DB都支持这些功能。 - JNevill

更正拼写并扩展标题 - Adam Porad

Answer 1

之前的回答并没有考虑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)

- Ben

---

你可能想要包括日期（=>，<=） - Artistan

另外，如果您使用==，则必须区分2行（t1.id！= t2.id）。 - Artistan

6年后，非常感谢！我一直在尝试使用GROUP BY、HAVING和WHERE EXISTS，但却陷入了困境，失去了希望。你的帖子立刻让我恍然大悟，10分钟后我就有了一个可行的查询。祝你好运。 - dimitarvp

Answer 2

如果每个开始日期小于或等于另一条记录的结束日期，则它将在范围内。

如果起始日期1小于结束日期2，并且起始日期2小于结束日期1。

 SELECT * 
 FROM t t1
    JOIN t t2 ON (t1.datefrom <= t2.dateto)
              AND (t2.datefrom <= t1.dateto)

- Guest

Answer 3

试试这个：

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)

感谢这个示例。经过修改，它正在工作：

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);

- Giorgi Nakeuri

---

不包括日期范围在另一个日期范围内的情况。 - Artistan

Answer 4

类似于@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)

- Artistan

Answer 5

在我的情况下，当表格中有大量数据时，更好的性能可以通过以下方式实现：

select *
from (
select id_customer,
       name,
       surname,
       date_from,
       date_to,
       lead(id_customer) over (partition by id_customer order by date_from) id_customer1,
       lead(name)        over (partition by id_customer order by date_from) name1,
       lead(surname)     over (partition by id_customer order by date_from) surname1,
       lead(date_from)   over (partition by id_customer order by date_from) date_from1,
       lead(date_to)     over (partition by id_customer order by date_from) date_to1
 from customer_wer)
where date_from1 <= date_to

- Sergey Afinogenov