题目不难 1.要点为 case when 分组,between 是闭区间所以不能用 2.timestampdiff不能用minute 而要用second 在除以六十,不然分钟差不准确 3.如何表示周几,可以使用date_format(," ") 其中常用的是 %Y 四位数表示年份 %m %d, %w不常用 显示周几 0为周日,6为周六
select
case when right(event_time,8) <"09:00:00" and right(event_time,8)>="07:00:00" then "早高峰"
when right(event_time,8) <"17:00:00" and right(event_time,8)>="09:00:00" then "工作时间"
when right(event_time,8) <"20:00" and right(event_time,8)>="17:00:00" then "晚高峰"
when right(event_time,8) <"07:00:00" or right(event_time,8)>="20:00:00" then "休息时间" end as period,
count(*) as num,
round(avg(timestampdiff(second,event_time,order_time)/60),1),
round(avg(timestampdiff(second,order_time,start_time)/60),1)
from tb_get_car_record re inner join tb_get_car_order using(uid,order_id)
where date_format(event_time,"%w") between 1 and 5
group by period
order by num