mysql - 使用mysql重叠间隔

Question

    +------+------------+------------+
    | id   | start_date | end_date   |
    +------+------------+------------+
    |    1 | 2019-01-01 | 2019-01-12 |
    |    1 | 2019-01-10 | 2019-01-27 |
    |    1 | 2019-01-13 | 2019-01-15 |
    |    1 | 2019-01-18 | 2019-01-25 |
    |    1 | 2019-02-10 | 2019-02-15 |
    |    2 | 2019-01-10 | 2019-01-15 |
    +------+------------+------------+

如何合并重叠间隔并在mysql(8.X)中得到以下结果？

    +------+------------+------------+
    | id   | start_date | end_date   |
    +------+------------+------------+
    |    1 | 2019-01-01 | 2019-01-27 |
    |    1 | 2019-02-10 | 2019-02-15 |
    |    2 | 2019-01-10 | 2019-01-15 |
    +------+------------+------------+

下面是可以在mysql中用来创建表的命令=>

    insert into interval_dates(id, start_date, end_date) values(1, '2019-01-01', '2019-01-12');
    insert into interval_dates(id, start_date, end_date) values(1, '2019-01-10', '2019-01-27');
    insert into interval_dates(id, start_date, end_date) values(1, '2019-01-13', '2019-01-15');
    insert into interval_dates(id, start_date, end_date) values(1, '2019-01-18', '2019-01-25');
    insert into interval_dates(id, start_date, end_date) values(1, '2019-02-10', '2019-02-15');
    insert into interval_dates(id, start_date, end_date) values(2, '2019-01-10', '2019-01-15');

你能分享任何优雅的解决方案而不插入mysql(8.X)中的中间表吗？

Answer 1

请检查:

SELECT id, start_date, MAX(end_date) end_date
FROM ( SELECT id,
              @p_start := CASE WHEN (start_date > @p_end) OR (@p_id < id)
                               THEN start_date
                               ELSE @p_start
                               END start_date,
              @p_end := CASE WHEN (end_date > @p_end) OR (@p_id < id)
                             THEN end_date
                             ELSE @p_end
                             END end_date,
              @p_id := id
       FROM mytable, ( SELECT @p_id := MIN(id)-1, 
                              @p_start := MIN(start_date) - INTERVAL 1 DAY, 
                              @p_end := MIN(start_date) - INTERVAL 1 DAY
                       FROM mytable ) variables
       ORDER BY id, start_date, end_date ) subquery
GROUP BY id, start_date;

fiddle (即使在 5.6 中也适用)。

我还没有找到产生错误结果的源数据。