生成时间段不交叉的数据。

生成时间段不交叉的数据。

设置四个变量，主要

DROP TABLE IF EXISTS `table`;CREATE TABLE `table` (  `orderID` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '订单ID',  `postedTime` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '执行时间',  `startTime` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '开始时间',  `endTime` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '完成时间',  `Name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '服务人员')   ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;用变量的来存储上一条数据的信息，然后当前生成数据和变量里的数据对比。SELECT orderID,Name,postedTime,    (@start := CASE WHEN @n = Name AND @d = postedTime                THEN TIMESTAMPADD(Minute, ROUND(RAND()*12)+8, @end)                ELSE TIMESTAMPADD(Minute, ROUND(RAND()*60), concat(postedTime,' 07:30:00'))                END) as starttimes,    (@end := TIMESTAMPADD(Minute, ROUND(RAND()*30)+30, @start)) as endtimes,    @n := esName,    @d := postedTimeFROM table A,(select @n :=null,@d :=null) XORDER BY Name, postedTime在update前设置变量。set @n := null, @d := null;update table a inner join (SELECT orderID,Name,postedTime,                    (@start := CASE WHEN @n = Name AND @d = postedTime                                            THEN TIMESTAMPADD(Minute, ROUND(RAND()*12)+8, @end)                                            ELSE TIMESTAMPADD(Minute, ROUND(RAND()*60), concat(postedTime,' 07:30:00'))                                            END) as starttimes,                    (@end := TIMESTAMPADD(Minute, ROUND(RAND()*30)+30, @start)) as endtimes,                    @n := Name,                    @d := postedTime                FROM table                ORDER BY expressName, postedTime) bon a.orderID = b.orderIDset     a.startTime = b.starttimes,    a.endTime = b.endtimes;