SELECT
foodid,nums,sum(quantity)
FROM (
SELECT
CASE
WHEN weighedtime >= concat_ws(" ",DATE_FORMAT(weighedtime,'%Y-%m-%d'),"00:00:01")
AND weighedtime < concat_ws(" ",DATE_FORMAT(weighedtime,'%Y-%m-%d'),"08:00:00") THEN
1
WHEN weighedtime >= concat_ws(" ",DATE_FORMAT(weighedtime,'%Y-%m-%d'),"08:00:01")
AND weighedtime < concat_ws(" ",DATE_FORMAT(weighedtime,'%Y-%m-%d'),"12:00:00") THEN
2
WHEN weighedtime >= concat_ws(" ",DATE_FORMAT(weighedtime,'%Y-%m-%d'),"12:00:01")
AND weighedtime < concat_ws(" ",DATE_FORMAT(weighedtime,'%Y-%m-%d'),"19:00:00") THEN
3
WHEN weighedtime >= concat_ws(" ",DATE_FORMAT(weighedtime,'%Y-%m-%d'),"19:00:01")
AND weighedtime < concat_ws(" ",DATE_FORMAT(weighedtime,'%Y-%m-%d'),"23:00:00") THEN
4
END as nums,
quantity,
foodid,
DATE_FORMAT(weighedtime,'%Y-%m-%d') as dtime
FROM
tab t where 1=1 order by t.weighedtime
) t group by nums,foodid limit 0,10
mysql 按时间段分组
最新推荐文章于 2024-08-02 04:11:46 发布