MySQL - 排除冲突的日期范围

Question

想象一下这个日期范围列表

> SELECT * FROM range_table;
+----+------------+------------+
| id | start      | end        |
+----+------------+------------+
|  1 | 2014-01-01 | 2014-01-15 | /* -- Not conflicting */
|  2 | 2014-01-15 | 2014-01-16 | /* -- Conflicting     */
|  3 | 2014-01-15 | 2014-01-20 | /* |                  */
|  4 | 2014-01-15 | 2014-01-19 | /* /                  */
|  5 | 2014-01-24 | 2014-01-26 | /* -- Conflicting     */
|  6 | 2014-01-21 | 2014-01-25 | /* /                  */
+----+------------+------------+

我正在尝试删除冲突范围并仅保留每个冲突集的第一次出现。

这是我最后应该得到的:

+----+------------+------------+
| id | start      | end        |
+----+------------+------------+
|  1 | 2014-01-01 | 2014-01-15 |
|  2 | 2014-01-15 | 2014-01-16 |
|  5 | 2014-01-24 | 2014-01-26 |
+----+------------+------------+

这是fiddle

Answer 1

SELECT x.*
  FROM range_table x
  LEFT 
  JOIN range_table y
    ON y.start < x.end 
   AND y.end > x.start 
   AND y.id < x.id
 WHERE y.id IS NULL;

http://sqlfiddle.com/#!2/6f723/26

查询已修改以适应明显修改的摘要