问题描述
我写了一个按小时计数的查询:
select TO_CHAR(copied_timestamp,'YYYY-MM-DD HH24'),从请求组计数(*)到
TO_CHAR(copied_timestamp,'YYYY-MM-DD HH24');
结果是:
2012-02-22 13 2280
2012-02-22 15 1250
2012-02-22 16 1245
2012-02-22 19 1258
但是我需要这样的结果:
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
另外我也有这些查询按日期和月份分组!
select TO_CHAR( TO_CHAR(copied_timestamp,'YYYY-MM-DD');从req
组计数(*)到TO_CHAR(copied_timestamp,'YYYY-MM-DD');
通过TO_CHAR(copied_timestamp,'YYYY-MM')选择TO_CHAR(copied_timestamp,'YYYY-MM'),从req
组中计数(*);
我需要填充零或零。
任何帮助都非常值得赞赏。
注意:
在oracle中使用 CONNECT BY
但我需要Mysql中的答案,因为Mysql不支持 CONNECT BY
。
我创建了一个名为TBL_NUMBERS的表
CREATE TABLE`TBL_NUMBER`(`n` int(11)NOT NULL)
并插入记录从1到1000.
现在我可以使用此查询生成任何类型的日期范围:
SELECT'2012-06-21'+ INTERVAL n-1 [DAY | HOUR | MINUTE]或as dateRange
FROM TBL_NUMBER
WHERE'2012-06-21'+ INTERVAL n-1 [DAY | HOUR | MINUTE]< ='2012-06-23';
然后我可以加入这张表格来填补日期空白。
如果我需要超过1000个日期范围,我可以在 TBL_NUMBER
中插入更多记录如果您有任何更好的主意,我渴望知道;)
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 ;)
这篇关于按小时,按小时或按天分组记录,并在mysql中用零或空填充空白的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!