包括在使用GROUP BY时缺少（零计数）行 [英] including missing (zero-count) rows when using GROUP BY

问题描述

我有一个接收短信的应用程序。我想做的是做一个统计与mysql将计数一个小时内的消息。例如在上午7点我收到10 sms消息，在上午8点我收到20等。我的表有此列ID，smsText，smsDate ...（其他不重要）。当我运行此脚本时：

I have an application that receives sms messages. What i want to do is make a statistic with mysql that will count meessages in a hour. For example in 7 am i received 10 sms messages, in 8 am i received 20 etc. My table has this columns ID, smsText, smsDate ... (others are not important). When i run this script:

SELECT HOUR(smsDate), COUNT(ID) FROM SMS_MESSAGES GROUP BY HOUR(smsDate)

它显示每小时收到的邮件数。问题是当我没有收到任何消息，例如在下午5点，这个语句不返回一个行17与计数0，我有这样的结果：

it show how many messages i get in every hour. The problem is when i dont receive any message for example in 5pm, this statement does't return a row 17 with count 0, and i have a result like this:

Hour Count
...
15 10
16 5
18 2
...

，我想得到的是

Hour Count
...
15 10
16 5
17 0
18 2
...

我在网上搜索一个解决方案，与UNION，但我不明白如何实现我的一个解决方案。希望有人可以帮助我。

I searched for a solution on the web, something with UNION but i don't understand how to implement that one in mine. Hope someone can help me.

推荐答案

您可以创建一个包含所有时间的表格并加入表格：

You could create a table with all hours and join the tables:

CREATE TABLE IF NOT EXISTS `hours` (
  `hour` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `hours` (`hour`) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23);

SELECT hours.hour, count( SMS_MESSAGES.ID ) 
FROM hours
LEFT JOIN SMS_MESSAGES ON ( hours.hour = HOUR( SMS_MESSAGES.smsDate ) ) 
GROUP BY 1 

这篇关于包括在使用GROUP BY时缺少（零计数）行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！