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

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;