日期分组SQL语句

[520NET]

表table1
字段1（日期） 字段2（数量）
2005-03-05 12
2005-03-05 14
2005-03-06 20
2005-03-07 12
2005-03-09 44
2005-03-09 14

要求按日期分组，统计每一天的数量，注：日期有可能不连续，不过缺省的日期显示数量为0。
结果如下
日期 数量
2005-03-05 26
2005-03-06 20
2005-03-07 12
2005-03-08 0
2005-03-09 58

[Andy__Huang]

日期范围你自己取定

select a.rq,sum(b.num) num
from
(select to_date('2005-03-'||rownum,'yyyy-mm-dd') rq from all_objects where rownum<=31)a
left join table1 b on a.rq=b.rq
where to_char(a.rq) between '2005-03-05' and '2005-03-09'
group by a.rq

[mosaic]

这个好像比较复杂，还涉及大小月，闰年啥的。

[阿三]

貌似这个http://topic.csdn.net/u/20090224/16/f14f173d-d686-4ada-9a51-b7f170ee7349.html这个贴子

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

会话已更改。

SQL> select * from c;

D1 I
------------------- ----------
2009-03-10 11:35:23 20
2009-03-10 11:35:29 22
2009-03-12 11:35:41 6
2009-03-13 11:35:50 102
2009-03-20 11:35:57 8

SQL> select d2,nvl(i,0) i
2 from ( select trunc(d1) d1,sum(i) i from c group by d1) a,
3 (select trunc(d2)+rownum as d2
4 from (select min(d1) d2,max(trunc(d1))-min(trunc(d1)) dc from c)
5 connect by rownum<dc) b
6 where a.d1(+)=b.d2
7 order by d2;

D2 I
------------------- ----------
2009-03-11 00:00:00 0
2009-03-12 00:00:00 6
2009-03-13 00:00:00 102
2009-03-14 00:00:00 0
2009-03-15 00:00:00 0
2009-03-16 00:00:00 0
2009-03-17 00:00:00 0
2009-03-18 00:00:00 0
2009-03-19 00:00:00 0

已选择9行。

[wmuthp]

select 日期，sum(数量）from 表table1 group by 日期

[阿三]

搞错了：（
SQL> select d2,nvl(i,0) i
2 from ( select trunc(d1) d1,sum(i) i from c group by trunc(d1)) a,
3 (select trunc(d2)+rownum-1 as d2
4 from (select min(d1) d2,max(trunc(d1))-min(trunc(d1))+1 dc from c)
5 connect by rownum <dc+1) b
6 where a.d1(+)=b.d2
7 order by d2;

D2 I
------------------- ----------
2009-03-10 00:00:00 42
2009-03-11 00:00:00 0
2009-03-12 00:00:00 6
2009-03-13 00:00:00 102
2009-03-14 00:00:00 0
2009-03-15 00:00:00 0
2009-03-16 00:00:00 0
2009-03-17 00:00:00 0
2009-03-18 00:00:00 0
2009-03-19 00:00:00 0
2009-03-20 00:00:00 8

[520NET]

解决了，谢谢！

[程序员叔叔]

对阿三超级顶礼膜拜。。。。