Mysql小时报表查询

Question

我想获取全 24 小时的每小时转化报告。我有这个查询，但它只返回 19 行而不是 24 行谁能告诉我这有什么问题吗？提前致谢。

SELECT   HOUR( `date_time` ) AS Hours, COUNT(conversion_id) AS `conversion` FROM conversions 
RIGHT JOIN (SELECT  0 AS Hour UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3 UNION ALL SELECT  4 UNION ALL SELECT  
    5 UNION ALL SELECT  6  UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9 UNION ALL SELECT 10 UNION ALL SELECT 11 
    UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL 
    SELECT 18  UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) AS AllHours 
ON HOUR(date_time) = Hour 
WHERE DATE(date_time) = CURDATE() OR date_time IS NULL 
GROUP BY Hour 
ORDER BY Hour

Answer 1

如果这一小时没有条目，则永远不会选择该时间。您必须反过来查询。

我认为它应该是这样的(没有数据库很难测试):

select * from (SELECT  0 AS Hour UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3 UNION ALL SELECT  4 UNION ALL SELECT       5 UNION ALL SELECT  6  UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9 UNION ALL SELECT 10 UNION ALL SELECT 11      UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL      SELECT 18  UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23)   as AllHours
left outer join 
    (select  COUNT(conversion_id) as cnt, HOUR(date_time) as h 
     FROM conversions 
     WHERE DATE(date_time) = CURDATE() OR date_time IS NULL 
     group by h) as c 
on Hour = e.h