一共两个版本， 一是补全的， 二是不补全的
使用时只需改把 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
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/4818.html
