mysql 零填充,MySQL：在使用GROUP BY时用零填充空字段

I've got MySQL table

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 |

And I'd like to get following:

| 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 |

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

One MySQL query as desired.