MySQL：在使用GROUP BY时用零填充空字段 [英] MySQL: filling empty fields with zeroes when using GROUP BY

问题描述

我有MySQL表

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

其中包含有关网站访问者的统计信息。

为了获得每小时的访问次数，我使用

which contains statistics about site visitors.
For getting visits per hour I use

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

这给了我

which gives me

| 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查询得到这样的结果？

How should I modify the query to get such result (with one mysql query, without creating temporary tables)?
Is it possible to get such result with one MySQL query?

推荐答案

答案。
也许我很疯狂，但这是有效的。

I've finaly found the answer. Maybe I'm insane, but this works.

SELECT HOUR, max(HOUR_STAT) as HOUR_STAT FROM (
    (
        SELECT HOUR(TIMESTAMP_X) as HOUR, count(*) as HOUR_STAT
        FROM cms_webstat
        WHERE date(TIMESTAMP_X) = date(now())
    )
    UNION (SELECT 0 as HOUR, 0)
    UNION (SELECT 1 as HOUR, 0)
    UNION (SELECT 2 as HOUR, 0)
    UNION (SELECT 3 as HOUR, 0)
    UNION (SELECT 4 as HOUR, 0)
    UNION (SELECT 5 as HOUR, 0)
    UNION (SELECT 6 as HOUR, 0)
    UNION (SELECT 7 as HOUR, 0)
    UNION (SELECT 8 as HOUR, 0)
    UNION (SELECT 9 as HOUR, 0)
    UNION (SELECT 10 as HOUR, 0)
    UNION (SELECT 11 as HOUR, 0)
    UNION (SELECT 12 as HOUR, 0)
    UNION (SELECT 13 as HOUR, 0)
    UNION (SELECT 14 as HOUR, 0)
    UNION (SELECT 15 as HOUR, 0)
    UNION (SELECT 16 as HOUR, 0)
    UNION (SELECT 17 as HOUR, 0)
    UNION (SELECT 18 as HOUR, 0)
    UNION (SELECT 19 as HOUR, 0)
    UNION (SELECT 20 as HOUR, 0)
    UNION (SELECT 21 as HOUR, 0)
    UNION (SELECT 22 as HOUR, 0)
    UNION (SELECT 23 as HOUR, 0)
)
AS `combined_table`
GROUP BY HOUR
ORDER BY HOUR DESC

一个MySQL查询。

这篇关于MySQL：在使用GROUP BY时用零填充空字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！