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

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

设置四个变量，主要

DROP TABLE IF EXISTS `ordertable`;
CREATE TABLE `ordertable` (
  `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 '服务人员',
  `orderAmount` 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 := postedTime
FROM table A,(select @n :=null,@d :=null) X
ORDER 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) b
on a.orderID = b.orderID
set 
    a.startTime = b.starttimes,
    a.endTime = b.endtimes;

下面是后来写的，目的是根据postTime，时间重置为不重叠(startTime,endTime)，且每天最早7：30到最晚18：30，如果超时就顺延一天
但是不知道哪里有问题，时间还是会超过18：30。
在检查和求答案中………………

如果有大神能指点下，在这里先谢谢…………

set @n := null,@d := null,@start := null,@end := null,@t := 0,@ttt := 0;
SELECT orderID,Name,postedTime,orderAmount,
    (@t := CASE WHEN @n = Name AND timediff(concat(DATE_ADD(postedTime,INTERVAL @t DAY),' 18:30'),@end) < 20000
                then @t + 1
                when  @n = Name and @d != postedTime and DATEDIFF(DATE_FORMAT(postedTime,"%Y-%m-%d"),DATE_FORMAT(@end,"%Y-%m-%d")) < 0
                then ABS(DATEDIFF(DATE_FORMAT(postedTime,"%Y-%m-%d"),DATE_FORMAT(@end,"%Y-%m-%d")))
                WHEN @n != Name or (@n = Name and @d != postedTime and DATEDIFF(DATE_FORMAT(postedTime,"%Y-%m-%d"),DATE_FORMAT(@end,"%Y-%m-%d")) > 0)
                then 0
                else CAST(@t as char)
                end) as t,
    (@start := CASE WHEN @n = Name
                                    AND ((@d = postedTime AND @t = @ttt) 
                                    OR (DATEDIFF(postedTime,@d) <= @ttt AND DATEDIFF(postedTime,@d) != 0 AND @ttt != 0))
                                    AND (timediff(concat(postedTime,' 18:30'),@end) > 20000 or timediff(concat(DATE_ADD(postedTime,INTERVAL @t DAY),' 18:30'),@end) > 20000) 
                                THEN (
                                    CASE WHEN (timediff(concat(DATE_FORMAT(@end,'%Y-%m-%d'),' 11:30'),@end) > 0) 
                                    THEN TIMESTAMPADD(Minute, ROUND(RAND()*20)+10, @end)
                                    ELSE TIMESTAMPADD(Minute, ROUND(RAND()*10)+30, @end)
                                    END
                                )
                            ELSE TIMESTAMPADD(Minute, ROUND(RAND()*20), concat(DATE_ADD(postedTime,INTERVAL @t DAY), ' 07:30'))
                            END) as starttimes,
    (@end := CASE WHEN orderAmount > 10000
                                THEN  TIMESTAMPADD(SECOND, ROUND(RAND()*1800)+9000, @start) 
                                ELSE TIMESTAMPADD(SECOND, ROUND(RAND()*3600)+3000, @start)
                                END) as endtimes,
    @ttt := @t,
    @n := Name,
    @d := postedTime
FROM ordertable
WHERE Name is not null AND Name!= '' AND postedTime > '2017-09-30' and postedTime < '2018-03-01'
ORDER BY Name, postedTime