按小时分组mysql 补齐,按小时，按小时或按天分组记录，并在mysql中用零或空填充空白...

I have written a query that counts records hour by hour:

select TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24'),count(*) from req group by

TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24');

the result is:

2012-02-22 13 2280

2012-02-22 15 1250

2012-02-22 16 1245

2012-02-22 19 1258

But I need a result like this:

2012-02-22 13 2280

2012-02-22 14 0

2012-02-22 15 1250

2012-02-22 16 1245

2012-02-22 17 0

2012-02-22 18 0

2012-02-22 19 1258

Also I have these queries that group by day and month too!

select TO_CHAR(copied_timestamp, 'YYYY-MM-DD'),count(*) from req

group by TO_CHAR(copied_timestamp, 'YYYY-MM-DD');

select TO_CHAR(copied_timestamp, 'YYYY-MM'),count(*) from req

group by TO_CHAR(copied_timestamp, 'YYYY-MM');

I need their gaps to be filled with zero or null too.

Any help is really appreciated.

Note:

There is an answer for this question in oracle using CONNECT BY but i need the answer in Mysql because Mysql does not support CONNECT BY.

Here is the link

解决方案

I created a table called TBL_NUMBERS

CREATE TABLE `TBL_NUMBER` (`n` int(11) NOT NULL)

and inserted records from 1 to 1000.

Now I can generate any kind of date range using this query:

SELECT '2012-06-21' + INTERVAL n-1 [DAY | HOUR | MINUTE] or as dateRange

FROM TBL_NUMBER

WHERE '2012-06-21' + INTERVAL n-1 [DAY | HOUR | MINUTE] <= '2012-06-23';

Then I can join this table with my results to fill the date gap.

If i need more than 1000 date range I can insert more records in TBL_NUMBER

If you have any better idea, I'm eager to know that ;)