用记录生成日期范围

我在此记录中有一个场景:

ID  emp  sdate               edate              datediff
10  54  2019-11-21 01:00:00 2019-11-22 01:00:00 1
11  54  2019-11-26 01:00:00 2019-11-27 01:00:00 1
12  54  2019-11-26 01:00:00 2019-11-27 01:00:00 1
13  54  2019-12-26 01:00:00 2019-12-29 01:00:00 3
14  54  2020-01-06 01:00:00 2020-01-10 01:00:00 4
15  54  2019-12-17 01:00:00 2019-12-17 19:00:00 0
16  54  2019-12-17 01:00:00 2019-12-17 22:00:00 0
17  54  2019-12-17 01:00:00 2019-12-17 23:00:00 0
18  527 2020-01-28 14:00:00 2020-01-28 15:00:00 0

All datediff greater than one i need to increment the sdate and edate something like this from the id = 13 and 14

13  54  2019-12-26 01:00:00 2019-12-29 01:00:00 3
14  54  2020-01-06 01:00:00 2020-01-10 01:00:00 4

我需要类似的结果

13  54 2019-12-26 01:00:00  2019-12-26 01:00:00
13  54 2019-12-27 01:00:00  2019-12-27 01:00:00
13  54 2019-12-28 01:00:00  2019-12-28 01:00:00
13  54 2019-12-29 01:00:00  2019-12-29 01:00:00
14  54 2020-01-06 01:00:00  2020-01-06 01:00:00
14  54 2020-01-07 01:00:00  2020-01-07 01:00:00
14  54 2020-01-08 01:00:00  2020-01-08 01:00:00
14  54 2020-01-09 01:00:00  2020-01-09 01:00:00
14  54 2020-01-10 01:00:00  2020-01-10 01:00:00

Is it possible that this 13 and 14 records to make like this?

任何答案和建议都非常感谢。 谢谢

评论
  • w无意
    w无意 回复

    这是一种使用递归查询的方法(仅在MySQL 8.0中可用):

    with recursive cte as (
        select id, emp, sdate, sdate + interval 1 day edate, datediff from mytable
        union all
        select id, emp, sdate + interval 1 day, edate + interval 1 day, datediff - 1 
        from cte 
        where datediff - 1 > 0
    )
    select id, emp, sdate, edate from cte
    order by id, sdate
    

    Demo on DB Fiddle:

    id | emp |日期|充实
    -:| -:| :------------------ | :------------------
    10 | 54 | 2019-11-21 01:00:00 | 2019-11-22 01:00:00
    11 | 54 | 2019-11-26 01:00:00 | 2019-11-27 01:00:00
    12 | 54 | 2019-11-26 01:00:00 | 2019-11-27 01:00:00
    13 | 54 | 2019-12-26 01:00:00 | 2019-12-27 01:00:00
    13 | 54 | 2019-12-27 01:00:00 | 2019-12-28 01:00:00
    13 | 54 | 2019-12-28 01:00:00 | 2019-12-29 01:00:00
    14 | 54 | 2020-01-06 01:00:00 | 2020-01-07 01:00:00
    14 | 54 | 2020-01-07 01:00:00 | 2020-01-08 01:00:00
    14 | 54 | 2020-01-08 01:00:00 | 2020-01-09 01:00:00
    14 | 54 | 2020-01-09 01:00:00 | 2020-01-10 01:00:00
    15 | 54 | 2019-12-17 01:00:00 | 2019-12-18 01:00:00
    16 | 54 | 2019-12-17 01:00:00 | 2019-12-18 01:00:00
    17 | 54 | 2019-12-17 01:00:00 | 2019-12-18 01:00:00
    18 | 527 | 2020-01-28 14:00:00 | 2020-01-29 14:00:00