mysql group by 空值_mysql:使用group by时用零填充空字段

我有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查询就可以得到这样的结果吗?