下面展示一些 内联代码片
。
一天24 小时分组查询
SELECT a.hour hour, ifnull(b.count, 0) count FROM (
SELECT 0 hour UNION ALL SELECT 1 hour UNION ALL SELECT 2 hour UNION ALL SELECT 3 hour UNION ALL SELECT 4 hour UNION ALL SELECT 5 hour UNION ALL SELECT 6 hour UNION ALL SELECT 7 hour UNION ALL SELECT 8 hour UNION ALL SELECT 9 hour UNION ALL SELECT 10 hour UNION ALL SELECT 11 hour UNION ALL SELECT 12 hour
UNION ALL SELECT 13 hour UNION ALL SELECT 14 hour UNION ALL SELECT 15 hour UNION ALL SELECT 16 hour UNION ALL SELECT 17 hour UNION ALL SELECT 18 hour UNION ALL SELECT 19 hour UNION ALL SELECT 20 hour UNION ALL SELECT 21 hour UNION ALL SELECT 22 hour UNION ALL SELECT 23 hour
) a LEFT JOIN
(
SELECT
hour(currtime) hour,
count(currtime) count
FROM car_base_state
WHERE electricity_ischarging = 1 AND date_format(currtime, '%Y-%m-%d') = current_date
GROUP BY date_format(currtime, '%Y%m%d-%H'), hour
) b
ON a.hour=b.hour
ORDER BY hour
var foo = 'bar';