oracle 查询

[w844638059]

表a
aid name
1 张三
2 李四
3 王五

表b
aid sum
1 12
2 22
2 56
3 98
3 65
3 33

查询出
aid name sum1 sum2 sum3
1 张三 12
2 李四 22 56
3 王五 98 65 33


查询语句怎么写

[cyousor]

Select t.Id,
       t.Name,
       Sum(Decode(Rn, 1, Num, Null)) Sum1,
       Sum(Decode(Rn, 2, Num, Null)) Sum2,
       Sum(Decode(Rn, 3, Num, Null)) Sum3
  From t,
       (Select s.Id,
               s.Num,
               Row_Number() Over(Partition By Id Order By Rownum) Rn
          From s) s
 Where t.Id = s.Id
 Group By t.Id, t.Name
 Order By Id