MySQL实战基础知识入门(2)：统计一天24小时数据默认补0的sql语句

语句分析：如果不存在某时段的数据，会显示为null，如何将不存在的时段自动补齐呢？

select a.lockdate,ifnull(b.count,0) as count
from (
   SELECT 0  as lockdate
    union all
    SELECT 1 as lockdate
    union all
    SELECT 2 as lockdate
    union all
    SELECT 3 as lockdate
    union all
    SELECT 4 as lockdate
    union all
    SELECT 5 as lockdate
    union all
    SELECT 6 as lockdate
		union all
    SELECT 7 as lockdate
		union all
    SELECT 8 as lockdate
		union all
    SELECT 9 as lockdate
		union all
    SELECT 10 as lockdate
		union all
    SELECT 11 as lockdate
		union all
    SELECT 12 as lockdate
		union all
    SELECT 13 as lockdate
		union all
    SELECT 14 as lockdate
		union all
    SELECT 15 as lockdate
		union all
    SELECT 16 as lockdate
		union all
    SELECT 17 as lockdate
		union all
    SELECT 18 as lockdate
		union all
    SELECT 19 as lockdate
		union all
    SELECT 20 as lockdate
		union all
    SELECT 21 as lockdate
		union all
    SELECT 22 as lockdate
		union all
    SELECT 23 as lockdate
) a left join (
	SELECT
		HOUR (login_time) AS hours
		FROM
			tb_log_login
		WHERE
			DATE_FORMAT(login_time, '%Y-%m-%d') = DATE_FORMAT('2020-07-31', '%Y-%m-%d')
		GROUP BY
		hours
) b on a.lockdate = b.hours;

Done！