mysql把一条数据转成多条数据

如下图，如果表1的startTime和表2的endTime日期间隔大于1，就根据它们的间隔天数拆分成对应条数的数据，例如图中，表1 的开始时间和结束时间，间隔天数为6，就拆分为6条数据，每条新数据的开始时间为第二天的00：00

with t as (
    select 0 as n
    union all select 1
    union all select 2
    union all select 3
    union all select 4
    union all select 5
    union all select 6
    union all select 7
    union all select 8
    union all select 9
),n as (
    select a.n+b.n*10+c.n*100 as n
    from t a
    cross join t b
    cross join t c
)
select a.id
    ,date_add(a.startTime,interval n.n day) as startTime
    ,date_add(a.startTime,interval n.n+1 day) as endTime
    ,date_format(date_add(a.startTime,interval n.n day),'%Y-%m-%d') as date
from (
    select 1 as id
        ,str_to_date('2020-9-14','%Y-%m-%d %H') as startTime
        ,str_to_date('2020-9-20','%Y-%m-%d %H') as endTime
) a
cross join n
where n.n<datediff(a.endTime,a.startTime);

对mysql不熟，不知道有哪个系统表有连续数字列，只好自己用cte弄个数字列

创建一个存储对象就行了，代码如下：

DROP PROCEDURE
IF
    EXISTS sum55;
CREATE PROCEDURE sum55 () BEGIN
    SET @c = ( SELECT created_at FROM organizes WHERE id = 1 );
    SET @e = ( SELECT updated_at FROM organizes WHERE id = 1 );
    SET @i = 1;

    REPEAT
      INSERT INTO organizes(created_at, updated_at)
        VALUES
            ( DATE_ADD(@c,INTERVAL @i DAY), DATE_ADD(@c,INTERVAL @i+1 DAY) );
        SET @i = @i + 1;
        UNTIL @i > DATEDIFF(@e,@c)-1 END REPEAT;
        SELECT * FROM organizes;
END;
CALL sum55 ();

如果对你有帮助，可以点击我这个回答右上方的【采纳】按钮，给我个采纳吗，谢谢