Mysql结果每小时数据

Question

我正在尝试计算每小时分组的条目数。我在不同的网站和此处找到了一些有用的信息:MySQL Group By Hours

但结果不是我所期望的。使用以下代码我得到:

SELECT   CONCAT(Hour, ':00-', Hour+1, ':00') AS Hours,
     COUNT(`usage_time`) AS `usage` FROM `usage`
  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(`usage_time`) = Hour

WHERE `usage_function` LIKE 'PlayedWholeSong' AND `usage_date` = DATE_SUB(CURDATE(), INTERVAL 0 DAY) OR `usage_time` IS NULL
GROUP BY Hour
ORDER BY Hour

结果:

Hours       usage
2:00-3:00   0
4:00-5:00   6
6:00-7:00   2
8:00-9:00   3
9:00-10:00  20
10:00-11:00 1
14:00-15:00 14
15:00-16:00 1
16:00-17:00 32
17:00-18:00 10

因为这些是今天的条目，所以我在 19:00 之后没有任何条目。此外，我没有看到 00:00 - 01:00、03:00 - 04:00 的条目，还有其他几个条目丢失了。但我确实想每 24 小时显示一个列表和结果，即使什么都没有。字符串是结果在 02:00 - 03:00 之间显示 0。我今天学到了很多关于 mysql 的知识，但没有解决我的问题。

我希望你能向我学习一些东西，不一定是代码，一个方向会很棒。

Answer 1

我个人更喜欢 LEFT JOIN 而不是 RIGHT JOIN。这样你就可以在你的 JOIN 中添加你的 WHERE 条件，它不会限制你的结果。试试这个:

SELECT   CONCAT(Hour, ':00-', Hour+1, ':00') AS Hours,
     COUNT(`usage_time`) AS `usage` 
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 JOIN `usage` ON HOUR(`usage_time`) = Hour 
           AND `usage_function` LIKE 'PlayedWholeSong' 
           AND `usage_date` = DATE_SUB(CURDATE(), INTERVAL 0 DAY) 
GROUP BY Hour
ORDER BY Hour

这是一个简化的 SQL Fiddle .

祝你好运。