如何MYSQL在24小时内每小时获取数据组，即使数据不存在，如果0将选择NULL

Question

例如，DATA 和 query 在此链接 SQL 小提琴

http://sqlfiddle.com/#!9/13aec/23 上可用

Answer 1

您需要修改where子句，因为它会过滤掉空记录：

where place_rvw.time between '2014-02-17' AND '2014-02-19'
or place_rvw.place_id is null

更新的 SQL 小提琴：http://sqlfiddle.com/#!9/13aec/27

您也可以通过修改其他人指出的left join条件来达到预期的结果，但我更喜欢where子句。

日期过滤器(place_rvw.time between '2014-02-17' AND '2014-02-19'(并不是连接两个表所需的条件，但它只是一个数据过滤器，所以它应该在where子句中。

Answer 2

使用

LEFT JOIN place_rvw ON jam.za_hour = HOUR(time) 
AND place_rvw.time between '2014-02-17' AND '2014-02-19'

必须完全删除 WHERE 子句。

小提琴

附言。我建议使用place_rvw.time >= '2014-02-17' AND place_rvw.time < '2014-02-19'.小提琴。原因 - 如果存在'2014-02-19 00:00:00'的数据，则在使用 BETWEEN 时将包含它。

Answer 3

您的查询不正确

SELECT 
jam.za_hour as `hour`, 
AVG(IFNULL(place_id,0)) as average_score
FROM (
SELECT 0 as za_hour
UNION
SELECT 1 as za_hour
UNION
SELECT 2 as za_hour
UNION
SELECT 3 as za_hour
UNION
SELECT 4 as za_hour
UNION
SELECT 5 as za_hour
UNION
SELECT 6 as za_hour
UNION
SELECT 7 as za_hour
UNION
SELECT 8 as za_hour
UNION
SELECT 9 as za_hour
UNION
SELECT 10 as za_hour
UNION
SELECT 11 as za_hour
UNION
SELECT 12 as za_hour
UNION
SELECT 13 as za_hour
UNION
SELECT 14 as za_hour
UNION
SELECT 15 as za_hour
UNION
SELECT 16 as za_hour
UNION
SELECT 17 as za_hour
UNION
SELECT 18 as za_hour
UNION
SELECT 19 as za_hour
UNION
SELECT 20 as za_hour
UNION
SELECT 21 as za_hour
UNION
SELECT 22 as za_hour
UNION
SELECT 23 as za_hour
) jam
LEFT OUTER JOIN place_rvw  ON (jam.za_hour = HOUR(time) AND (DATE(time) BETWEEN '2014-02-01' and '2014-02-28'))
GROUP BY
jam.za_hour
ORDER BY jam.za_hour;
select * from place_rvw

如果您在 where 条件之间使用它会清除其他所有内容，则需要在打开条件中添加此条件。