WITH t AS ( SELECT CASE WHEN TIME(event_time)>='07:00:00' AND TIME(event_time)<'09:00:00' THEN '早高峰' WHEN TIME(event_time)>='09:00:00' AND TIME(event_time)<'17:00:00' THEN '工作时间' WHEN TIME(event_time)>='17:00:00' AND TIME(event_time)<'20:00:00' THEN '晚高峰' ELSE '休息时间' END period, TIMESTAMPDIFF(SECOND,event_time,end_time) wait_time, TIMESTAMPDIFF(SECOND,order_time,start_time) dispatch_time FROM tb_get_car_record r JOIN tb_get_car_order o ON r.order_id = o.order_id WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6 ) SELECT period,COUNT(*) get_car_num, ROUND(AVG(wait_time)/60,1) avg_wait_time, ROUND(AVG(dispatch_time)/60,1) acg_dispatch_time FROM t GROUP BY period ORDER BY get_car_num