我想获得所有 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
如果此小时没有条目,则永远不会选择该条目。您必须反其道而行之。
我认为它应该是这样的(没有您的数据库很难测试):
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
right join
几乎是正确的。 我更喜欢where
条件在 on
子句中(而不是检查NULL
值。 不过,关键是使用select
中的AllHours
表,并group by
:
SELECT AllHours.Hour 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(conversions.date_time) = AllHours.Hour and DATE(conversions.date_time) = CURDATE()
GROUP BY AllHOurs.Hour
ORDER BY AllHours.Hour