DB----按小时统计，小时为空，用0补充

按小时统计，小时为空，用0补充

SQL

SELECT dayHour day, IF(count IS NULL, 0, count) as count FROM (SELECT sum(o.paid_amount) AS count, date_format(o.pay_time,'%H') AS hour
        FROM crm_order o
        where o.pay_time is not null and date_format(o.pay_time, '%Y%m%d') = date_format(NOW(),'%Y%m%d')
        GROUP BY hour ORDER BY 1) A
        RIGHT JOIN (SELECT one.hours + two.hours AS dayHour
        FROM (SELECT 0 hours
            UNION ALL SELECT 1 hours
            UNION ALL SELECT 2 hours
            UNION ALL SELECT 3 hours
            UNION ALL SELECT 4 hours
            UNION ALL SELECT 5 hours
            UNION ALL SELECT 6 hours
            UNION ALL SELECT 7 hours
            UNION ALL SELECT 8 hours
            UNION ALL SELECT 9 hours) one
        CROSS JOIN (SELECT 0 hours
            UNION ALL SELECT 10 hours
            UNION ALL SELECT 20 hours) two
        WHERE (one.hours + two.hours) <![CDATA[ < ]]> 24) B ON A.hour = CONVERT(B.dayHour, SIGNED) ORDER BY day

图表展示

直接命令运行sql