表中数据:
id name fee month
1 s 100 200711
1 s 200 200710
1 s 300 200709
2 a 100 200711
2 a 200 200710
2 a 3000 200709
3 m 1000 200709
要求实现的结果:
1 s 200709 100 200710 200 200711 300
2 a 200709 3000 200710 200 200711 100
3 m 200709 1000
数据库:oracle9i
请大家给写个语句,不想用decode!
id name fee month
1 s 100 200711
1 s 200 200710
1 s 300 200709
2 a 100 200711
2 a 200 200710
2 a 3000 200709
3 m 1000 200709
要求实现的结果:
1 s 200709 100 200710 200 200711 300
2 a 200709 3000 200710 200 200711 100
3 m 200709 1000
数据库:oracle9i
请大家给写个语句,不想用decode!
2 union all
3 select 1 id,'s' name,200 fee,'200710' month from dual
4 union all
5 select 1 id,'s' name,300 fee,'200709' month from dual
6 union all
7 select 2 id,'a' name,100 fee,'200711' month from dual
8 union all
9 select 2 id,'a' name,200 fee,'200710' month from dual
10 union all
11 select 2 id,'a' name,3000 fee,'200709' month from dual
12 union all
13 select 3 id,'m' name,1000 fee,'200709' month from dual
14 )
15 select id,name,max(decode(rn,1,month,null)) month1,
16 max(decode(rn,1,fee,null)) fee1,
17 max(decode(rn,2,month,null)) month2,
18 max(decode(rn,2,fee,null)) fee2,
19 max(decode(rn,3,month,null)) month3,
20 max(decode(rn,3,fee,null)) fee3
21 from (select id,name,month,fee,row_number()over(partition by id order by month) rn from a)
22 group by id,name
23 order by id
24 /
ID NAME MONTH1 FEE1 MONTH2 FEE2 MONTH3 FEE3
---------- ---- ------ ---------- ------ ---------- ------ ----------
1 s 200709 300 200710 200 200711 100
2 a 200709 3000 200710 200 200711 100
3 m 200709 1000