MySQL 补全24小时缺失数据为0

一共两个版本， 一是补全的， 二是不补全的
使用时只需改把 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 &gt; #{dto.startTime}
        </if>
        <if test="dto.endTime != null">
            AND create_time &lt; #{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 &gt; #{dto.startTime}
        </if>
        <if test="dto.endTime != null">
            AND create_time &lt; #{dto.endTime}
        </if>

        GROUP BY times