表中有几个字段 其中一个日期字段a_date(YYYY-MM-DD) 数量a_num 数量b_num不是每天日期都有 如: a_date a_num b_num
2011-09-01 2000 2000
2011-09-01 1000 2000
2011-09-03 4000 3000
2011-09-06 6000 4000
2011-09-06 2000 4000
2011-09-07 3000 5000
2011-09-08 2000 2000
2011-09-10 1000 2000
...
2011-09-30 1000 1000输入查询条件为: YYYY-MM(2011-09)
要求:查询每天的数量
日期 a_num b_num
1 3000 4000
2 0 0
3 4000 3000
4 0
....
30 1000 1000
2011-09-01 2000 2000
2011-09-01 1000 2000
2011-09-03 4000 3000
2011-09-06 6000 4000
2011-09-06 2000 4000
2011-09-07 3000 5000
2011-09-08 2000 2000
2011-09-10 1000 2000
...
2011-09-30 1000 1000输入查询条件为: YYYY-MM(2011-09)
要求:查询每天的数量
日期 a_num b_num
1 3000 4000
2 0 0
3 4000 3000
4 0
....
30 1000 1000
with tbl as
(
select '2011-09-01' as a_date, 2000 as a_num, 2000 as b_num from dual
union all
select '2011-09-01' as a_date, 1000 as a_num, 2000 as b_num from dual
union all
select '2011-09-03' as a_date, 4000 as a_num, 3000 as b_num from dual
)
select b.sdate, nvl(sum(a_num), 0) as a_num, nvl(sum(b_num), 0) as b_num
from tbl a right join (select to_char(to_date('2011-09', 'YYYY-MM') + rownum - 1, 'YYYY-MM-DD') as sdate from dual
connect by rownum <= to_number(to_char(last_day(to_date('2011-09', 'YYYY-MM')), 'dd'))) b
on a.a_date = b.sdate
group by b.sdate
order by b.sdate;
select to_number(to_char(a_date,'dd')) 日期,nvl(sum(a_num),0),nvl(sum(b_num),0) from table t where to_char(a_date,'yyyy-mm')='2011-09' group by a_date order by to_number(to_char(a_date,'dd')) ;
发帖上面说明了 查询要求结果,查询出从1-30号每天的数量 没出现就显示为0 这下懂没?你查询的结果是 正对出现的日期进行分组排序,没出现的则在查询结果中不会出现日期 a_num b_num
1 3000 4000
2 0 0
3 4000 3000
4 0
....
30 1000 1000