本文介绍了按小时,按小时或按天分组记录,并在mysql中用零或空填充空白的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我写了一个按小时计数的查询:

  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中用零或空填充空白的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-27 07:46