mysql 查询24小时内 数据，null用0代替

获取24小时数据总数，但是当某一个小时内没有产生数据时，GROUP BY就筛选不出来那个时间段的数据，但实际需求是要用0代替。解决方法如下：

SELECT
  dayHour,
  IF(count IS NULL, 0, count)
FROM (SELECT
        count(*) AS count,
        DATE_FORMAT(create_time, '%H')
                 AS hour
      FROM xxxx//你的表
      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) < 24) B ON A.hour = CONVERT(B.dayHour, SIGNED)
ORDER BY dayHour

//这下面可以生成一个0-23的表，再结合right join 就可以将自己的表查询关联起来，再使用

IF(count IS NULL, 0, count) 就可以了

(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) < 24) B