create table test(j varchar2(10),k varchar2(10),l date);
insert into test values('oracle','xun',to_date('2010-11','YYYY-MM'));
insert into test values('java','luxun',to_date('2010-11','YYYY-MM'));
insert into test values('c','xun',to_date('2010-11','YYYY-MM'))
insert into test values('c','xun',sysdate);
insert into test values('java','xun',sysdate);
insert into test values('c++','xun',sysdate); SELECT b.l 月份, nvl(a.amount, 0) 数量
FROM (select to_char(l, 'mm') l, count(*) amount
from test
group by to_char(l, 'mm')) a,
(SELECT lpad(to_char(LEVEL), 2, '0') l
FROM dual
CONNECT BY LEVEL <= 12) b
WHERE b.l = a.l(+)
ORDER BY b.l;
/*
月份 数量
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 3
12 0
*/
CONNECT BY LEVEL 实现12月统计数据
最新推荐文章于 2019-01-13 15:02:31 发布