一共两个版本, 一是补全的, 二是不补全的
使用时只需改把 report_time 改成你的数据库时间字段, city_task表也更改下即可
select a.times,ifnull(b.count,0) as num
from (
SELECT "00:00" as times
union all
SELECT "01:00" as times
union all
SELECT "02:00" as times
union all
SELECT "03:00" as times
union all
SELECT "04:00" as times
union all
SELECT "05:00" as times
union all
SELECT "06:00" as times
union all
SELECT "07:00" as times
union all
SELECT "08:00" as times
union all
SELECT "09:00" as times
union all
SELECT "10:00" as times
union all
SELECT "11:00" as times
union all
SELECT "12:00" as times
union all
SELECT "13:00" as times
union all
SELECT "14:00" as times
union all
SELECT "15:00" as times
union all
SELECT "16:00" as times
union all
SELECT "17:00" as times
union all
SELECT "18:00" as times
union all
SELECT "19:00" as times
union all
SELECT "20:00" as times
union all
SELECT "21:00" as times
union all
SELECT "22:00" as times
union all
SELECT "23:00" as times
) a left join (
SELECT
DATE_FORMAT(report_time, '%H:00') AS hours,
count(*) AS count
FROM
city_task
WHERE
! ISNULL(report_time)
<if test="dto.startTime != null">
AND create_time > #{dto.startTime}
</if>
<if test="dto.endTime != null">
AND create_time < #{dto.endTime}
</if>
GROUP BY
hours
) b on a.times = b.hours
ORDER BY a.times
;
SELECT
DATE_FORMAT(report_time, '%H:00') times,
count(*) num
FROM
city_task
WHERE
! ISNULL(report_time)
<if test="dto.startTime != null">
AND create_time > #{dto.startTime}
</if>
<if test="dto.endTime != null">
AND create_time < #{dto.endTime}
</if>
GROUP BY times