我用这段SQL实现统计数据库中存在的时间段内每一天的记录数但是当我年份存在错误的时候,例如出现0006-11-11这个年份,他就从0006开始按天统计,
但是不能统计到2008年,只能达到0006-0090时间段内的每一天
请问不能返回所有的结果集是什么原因?SQL> select * from test_time; TID DATATIME
---------- -----------
1 2008-1-1 1:
2 2008-1-1 7:
3 2008-1-1 9:
4 2008-1-2 11
5 2008-1-2 12
6 2008-1-2 17
7 2008-1-2 21
8 2008-1-4 1:
9 2008-1-4 5:
10 2008-1-4 14
11 2008-1-4 16
12 2008-1-4 2212 rows selectedSQL>
SQL> select a_time, nvl(counts, 0)
2 from (select trunc(datatime) c_time, count(1) counts
3 from test_time
4 group by trunc(datatime)) count_time,
5 (select min_time + rownum - 1 a_time
6 from all_objects,
7 (select min(trunc(datatime)) min_time,
8 max(trunc(datatime)) max_time
9 from test_time) loop_time
10 where rownum < = max_time - min_time + 1) all_time
11 where a_time = c_time(+);A_TIME NVL(COUNTS,0)
----------- -------------
2008-1-1 3
2008-1-2 4
2008-1-3 0
2008-1-4 5
但是不能统计到2008年,只能达到0006-0090时间段内的每一天
请问不能返回所有的结果集是什么原因?SQL> select * from test_time; TID DATATIME
---------- -----------
1 2008-1-1 1:
2 2008-1-1 7:
3 2008-1-1 9:
4 2008-1-2 11
5 2008-1-2 12
6 2008-1-2 17
7 2008-1-2 21
8 2008-1-4 1:
9 2008-1-4 5:
10 2008-1-4 14
11 2008-1-4 16
12 2008-1-4 2212 rows selectedSQL>
SQL> select a_time, nvl(counts, 0)
2 from (select trunc(datatime) c_time, count(1) counts
3 from test_time
4 group by trunc(datatime)) count_time,
5 (select min_time + rownum - 1 a_time
6 from all_objects,
7 (select min(trunc(datatime)) min_time,
8 max(trunc(datatime)) max_time
9 from test_time) loop_time
10 where rownum < = max_time - min_time + 1) all_time
11 where a_time = c_time(+);A_TIME NVL(COUNTS,0)
----------- -------------
2008-1-1 3
2008-1-2 4
2008-1-3 0
2008-1-4 5
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货