现有数据表如下:
id insertdate content
1 20070601 eee
2 20070601 aaa
3 20070605 bbb
4 20070607 ttt
5 20070607 aaa
6 20070611 aaa
7 20070611 aaa
8 20070505 bbb
9 20070506 ttt
10 20070506 aaa
11 20070521 aaa
12 20070521 aaa希望得到结果如下:日期 200705(条数) 200706(条数)
1 0 2
2 0 0
3 0 0
4 0 0
5 1 1
6 2 0
7 0 2
8 0 0
9 0 0
10 0 0
11 0 2
。
21 2 0
22 0 0
。
31 0 0请大家看看
count(select *
from table
where to_char(insertdate,'YYYYMMDD')='200705') 200705(条数),
count(select *
from table
where to_char(insertdate,'YYYYMMDD')='200706') 200706(条数)
from table
group by 日期
(
select to_number(substr(insertdate, 7, 2)) rq,
sum(decode(substr(insertdate, 1, 4), 2006, 1, 0)) y2006,
sum(decode(substr(insertdate, 1, 4), 2007, 1, 0)) y2007
from czd_t01
group by substr(insertdate, 7, 2)
) a,
(select rownum num from dual connect by rownum<32) b
where b.num=a.rq(+)
(
select to_number(substr(insertdate, 7, 2)) rq,
sum(decode(substr(insertdate, 1, 6), 200705, 1, 0)) y200705,
sum(decode(substr(insertdate, 1, 6), 200706, 1, 0)) y200706
from czd_t01
group by substr(insertdate, 7, 2)
) a,
(select rownum num from dual connect by rownum<32) b
where b.num=a.rq(+)上面的看错条件了