select distinct(b.gonghao),a.xingm,
(select sum(jine) from a2 where (gonghao = a.gonghao) and (riq >='2004-01-01') and (riq <'2004-02-01')) as '1月',
(select sum(jine) from a2 where (gonghao = a.gonghao) and (riq >='2004-02-01') and (riq <'2004-03-01')) as '2月',
(select sum(jine) from a2 where (gonghao = a.gonghao) and (riq >='2004-03-01') and (riq <'2004-04-01')) as '3月'
from a1 a,a2 b where a.gonghao = b.gonghao
SQL>
SQL> select a.xm,a.gh,b.* from bb a,(
2 select gh,sum(m8),sum(m9),sum(m10) from(
3 select gh,je,
4 max(decode(trunc(sj, 'month'),to_date('2004-08-01','yyyy-mm-dd'),je,0)) m8,
5 max(decode(trunc(sj, 'month'),to_date('2004-09-01','yyyy-mm-dd'),je,0)) m9,
6 max(decode(trunc(sj, 'month'),to_date('2004-10-01','yyyy-mm-dd'),je,0)) m10
7 from(
8 select gh,sum(je) as je,sj
9 from aa group by gh,sj)
10 group by gh,je
11 )
12 group by gh) b
13 where a.gh=b.gh(+) ;
SQL>
SQL> select a.xm,a.gh,b.* from bb a,(
2 select gh,sum(m8),sum(m9),sum(m10) from(
3 select gh,je,
4 max(decode(trunc(sj, 'month'),to_date('2004-08-01','yyyy-mm-dd'),je,0)) m8,
5 max(decode(trunc(sj, 'month'),to_date('2004-08-01','yyyy-mm-dd'),je,0)) m9,
6 max(decode(trunc(sj, 'month'),to_date('2004-08-01','yyyy-mm-dd'),je,0)) m10
7 from(
8 select gh,sum(je) as je,sj
9 from aa group by gh,sj)
10 group by gh,je
11 )
12 group by gh) b
13 where a.gh=b.gh(+) ;
SQL>
SQL> select a.xm,b.* from bb a,(
2 select gh,sum(je),sum(m1),sum(m2),sum(m3) from(
3 select gh,je, max(decode(sj,'200401',je,0)) m1,
4 max(decode(sj,'200402',je,0)) m2,
5 max(decode(sj,'200403',je,0)) m3
6 from(
7 select gh,sum(je) as je,sj
8 from aa group by gh,sj)
9 group by gh,je
10 )
11 group by gh) b
12 where a.gh=b.gh(+)
13 ;
然后再进行一次求和,最后的语句应该是这样的,12个月的一次望上加
SQL>
SQL> select gh,sum(je),sum(m1),sum(m2),sum(m3),sum(m4) from(
2 select gh,je, max(decode(sj,'200401',je,0)) m1,
3 max(decode(sj,'200402',je,0)) m2,
4 max(decode(sj,'200403',je,0)) m3,
5 max(decode(sj,'200404',je,0)) m4
6 from(
7 select gh,sum(je) as je,sj
8 from aa group by gh,sj)
9 group by gh,je
10 )
11 group by gh
12 ;
SQL>
SQL> select gh,je, max(decode(sj,'200401',je,0)) m1,
2 max(decode(sj,'200402',je,0)) m2,
3 max(decode(sj,'200403',je,0)) m3,
4 max(decode(sj,'200404',je,0)) m4
5 from(
6 select gh,sum(je) as je,sj
7 from aa group by gh,sj)
8 group by gh,je
9 ;