yuanpeng 发表于 2012-3-14 10:58
结果是我自己手写的呵呵,不知道怎么达到这种情况,在实际应用中需要这种不累加的,比如说1个人在1月份即是 ...
LZ, 你测试一下如下的语句吧, 我是猜测的!
[注: 如果你需要好几个月份,那你自己改改如下的代码, 并且把“where month <> '201102'”中‘201102'值改为最大你需要的月份即可]
SQL> select * from t2;
ID MONTH JIAGE
---------- ------ ----------
1 201101 10
1 201101 12
1 201102 20
2 201101 15
2 201102 30
3 201101 21
6 rows selected
SQL>
SQL> select id,
2 "201101",
3 "201102"
4 from
5 (select id,
6 month,
7 rn,
8 sum(decode(month, '201101', jiage, 0)) "201101",
9 sum(sum(decode(month, '201102', jiage, 0))) over(partition by id) "201102"
10 from
11 (select id,
12 month,
13 jiage,
14 decode(dense_rank() over(order by id), 1, row_number() over(partition by id order by month), id) rn
15 from t2)
16 group by id, month, rn)
17 where month <> '201102';
ID 201101 201102
---------- ---------- ----------
1 10 20
1 12 20
2 15 30
3 21 0
SQL>