MySql 全天候查询结果

我需要获取一天中所有时间的数据，即使计数为 0。 现在它输出：

clicks--hour
 1----- 7
 2----- 13

我现在的查询：

SELECT count(*) as clicks, hour(time) as hour FROM clicks WHERE DATE(time) = DATE(NOW()) GROUP BY hour

感谢您的时间

解决了，设法自己做，以某种方式。

SELECT h.Hour
     , COUNT(id) AS clicks
  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 h
LEFT OUTER
  JOIN clicks
    ON hour(time) = h.Hour
   AND DATE(time) = date(now())
GROUP 
    BY h.Hour

您可以使用COALESCE此命令有助于在 select 返回 null 的情况下将 null 替换为如下值：

SELECT 
    COALESCE(count(*), 0) as clicks, 
    COALESCE(hour(time), hour(now())) as hour 
FROM 
    clicks 
WHERE DATE(time) = DATE(NOW()) GROUP BY hour

希望这就是您正在寻找的。