sql--查询一天内每小时的数据

CREATE TABLE `date_tool` (
  `hour` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

SELECT
dt. HOUR,
ifNULL(s.number, 0) AS number
FROM
date_tool dt
LEFT JOIN (
SELECT
HOUR (r.capture_time) AS HOUR,
count(*) AS number
FROM
md_capture_record r
WHERE
DATE_FORMAT(r.capture_time, ‘%y-%M-%d‘) = DATE_FORMAT(NOW(), ‘%y-%M-%d‘)
GROUP BY
HOUR
) s ON dt.`hour` = s. HOUR
ORDER BY
dt.`hour`