注意时间为毫秒的时间戳。
SELECT
case
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 0 then '0'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 1 then '1'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 2 then '2'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 3 then '3'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 4 then '4'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 5 then '5'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 6 then '6'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 7 then '7'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 8 then '8'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 9 then '9'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 10 then '10'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 11 then '11'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 12 then '12'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 13 then '13'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 14 then '14'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 15 then '15'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 16 then '16'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 17 then '17'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 18 then '18'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 19 then '19'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 20 then '20'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 21 then '21'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 22 then '22'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 23 then '23'
end as name, count(id) as total
FROM goods_t as g WHERE (DAY(FROM_UNIXTIME(g.time_s/1000, '%Y-%m-%d')) = DAY(now())) and g.consume = 1 group by
case
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 0 then '0'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 1 then '1'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 2 then '2'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 3 then '3'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 4 then '4'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 5 then '5'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 6 then '6'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 7 then '7'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 8 then '8'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 9 then '9'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 10 then '10'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 11 then '11'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 12 then '12'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 13 then '13'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 14 then '14'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 15 then '15'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 16 then '16'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 17 then '17'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 18 then '18'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 19 then '19'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 20 then '20'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 21 then '21'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 22 then '22'
when FROM_UNIXTIME(wc.time_s/1000, '%H') = 23 then '23'
end
order by null;