mysql - 如何查找日期之间重叠的记录

Question

我正在使用MySQL，我有以下表结构

Id  id2 classId sectionId   validFrom   validTill
------------------------------------------------------
1   1       5       13      2016-01-01  2016-03-30
2   1       5       22      2016-01-15  2016-03-30
3   1       5       23      2016-01-15  2016-04-29
4   1       5       13      2016-04-01  2016-04-30
9   10      6       24      2016-01-17  2016-02-05
10  10      6       25      2016-01-23  2016-02-05
11  10      6       24      2016-01-31  2016-02-05

我的SQL语句是

SELECT count(*) as timeCount FROM TimeTableClassSection a 
WHERE classId=5 AND sectionId=13 AND ((a.ValidFrom BETWEEN '2016-01-18' AND '2016-01-24') OR (a.ValidTill BETWEEN '2016-01-18' AND '2016-01-24'))

返回timeCount = 0。但它应该返回 1，因为 Id=1 的记录在此日期范围内 ('2016-01-18' AND '2016-01-24')

我试图实现，找出提供的日期范围内特定 classId 和sectionId 的任何重叠记录。如果 classId=5 和 sectionId=13 具有 validFrom=2016-01-01 validTill=2016-03-30 存在，则此日期范围 ('2016-01-18' AND '2016-01-24') 之间的任何日期范围都应将此记录作为计数抛出。

If I give date range 2015-12-25 to 2016-09-20 then record count should = 1
If I give date range 2016-2-1 to 2016-02-20 then record count should = 1
If I give date range 2016-2-1 to 2016-09-20 then record count should = 1

我在这里做错了什么...所有日期格式都是 YYYY-MM-DD

Answer 1

您仅检查边界是否在日期范围内，但不检查数据范围是否在边界内。您应该扩展您的位置标准:

...AND ((a.ValidFrom BETWEEN '2016-01-18' AND '2016-01-24')
       OR (a.ValidTill BETWEEN '2016-01-18' AND '2016-01-24')
       OR (a.ValidFrom<'2016-01-18' AND a.ValidTill>'2016-01-24'))