比如表中数据是
time count
2012-01-01 1000
2012-01-02 500
2012-02-01 800
2012-03-05 2000要查询出来
1月 1500
2月 800
3月 2000
4月 0
5月 0
刚开始学这个 不怎么会 这样说可以理解吗 麻烦各位了
time count
2012-01-01 1000
2012-01-02 500
2012-02-01 800
2012-03-05 2000要查询出来
1月 1500
2月 800
3月 2000
4月 0
5月 0
刚开始学这个 不怎么会 这样说可以理解吗 麻烦各位了
sum(t.count) sumy
from tab t
group by to_char(t.time,'yyyy-mm');
create table t1(d_date date,d_count number);insert into t1 values (date'2012-01-01',1000);
insert into t1 values (date'2012-01-02',500);
insert into t1 values (date'2012-01-03',500);
insert into t1 values (date'2012-02-01',800);
insert into t1 values (date'2012-02-02',200);
insert into t1 values (date'2012-03-01',300);
insert into t1 values (date'2012-04-01',400);
insert into t1 values (date'2012-07-01',700);
select to_char(a.c1,'yyyy-mm') 月份,nvl(sum(t1.d_count),0) 数量
from
(SELECT ADD_MONTHS(DATE'2011-12-01',LEVEL) AS c1
FROM DUAL
CONNECT BY LEVEL <= 12) a left join t1 on to_char(a.c1,'yyyy-mm')=to_char(t1.d_date,'yyyy-mm')
group by to_char(a.c1,'yyyy-mm')
order by to_char(a.c1,'yyyy-mm') 月份 数量
-----------------------------------------
1 2012-01 2000
2 2012-02 1000
3 2012-03 300
4 2012-04 400
5 2012-05 0
6 2012-06 0
7 2012-07 700
8 2012-08 0
9 2012-09 0
10 2012-10 0
11 2012-11 0
12 2012-12 0
--构建1年的12个月
SELECT ADD_MONTHS(DATE'2011-12-01',LEVEL) AS c1
FROM DUAL
CONNECT BY LEVEL <= 12--构建一个月的日期
select to_char(to_date('2012-03', 'YYYY-MM') + rownum - 1, 'YYYY-MM-DD') as sdate from dual
connect by rownum <= to_number(to_char(last_day(to_date('2012-03', 'YYYY-MM')), 'dd'))