MySQL:使用 GROUP BY 时用零填充空字段

Question

我有 MySQL 表

CREATE TABLE cms_webstat (
    ID int NOT NULL auto_increment PRIMARY KEY,
    TIMESTAMP_X timestamp DEFAULT CURRENT_TIMESTAMP,
    # ... some other fields ...
)

其中包含有关网站访问者的统计信息。
为了每小时获得访问量，我使用

SELECT
    hour(TIMESTAMP_X) as HOUR
    , count(*) AS HOUR_STAT
FROM cms_webstat
GROUP BY HOUR
ORDER BY HOUR DESC

这给了我

| HOUR | HOUR_STAT |
|  24  |    15     |
|  23  |    12     |
|  22  |    9      |
|  20  |    3      |
|  18  |    2      |
|  15  |    1      |
|  12  |    3      |
|   9  |    1      |
|   3  |    5      |
|   2  |    7      |
|   1  |    9      |
|   0  |    12     |

我想获得以下信息:

| HOUR | HOUR_STAT |
|  24  |    15     |
|  23  |    12     |
|  22  |    9      |
|  21  |    0      |
|  20  |    3      |
|  19  |    0      |
|  18  |    2      |
|  17  |    0      |
|  16  |    0      |
|  15  |    1      |
|  14  |    0      |
|  13  |    0      |
|  12  |    3      |
|  11  |    0      |
|  10  |    0      |
|   9  |    1      |
|   8  |    0      |
|   7  |    0      |
|   6  |    0      |
|   5  |    0      |
|   4  |    0      |
|   3  |    5      |
|   2  |    7      |
|   1  |    9      |
|   0  |    12     |

我应该如何修改查询以获得这样的结果(使用一个 mysql 查询，而不创建临时表)？
是否可以通过一个 MySQL 查询获得这样的结果？

Answer 1

创建另一个只有一列的表，

CREATE TABLE hours_list (
    hour int NOT NULL PRIMARY KEY
)

填写所有 24 小时。

然后对该表进行连接以填充零。

SELECT
    hs.hour as HOUR, COUNT(ws.ID) AS HOUR_STAT
FROM hours_list hs 
LEFT JOIN cms_webstat ws ON hs.hour = hour(ws.TIMESTAMP_X)
GROUP BY hs.hour
ORDER BY hs.hour DESC