这个sql怎么写。

Question

表 a
type 有三种 a，b，c
pid         type         amount              date
101           a             20                20010101
103           b               5               20010103
101           c              4                20010104
101           a             3                 20010103
103           c            5                  20010201根据表a生成 表b，其中是根据pid分组汇总各自的type为a，b，c的总数，然后统计各id的type的总数allAmount
pid         typeA typeB typeC     allAmount
101          23    0     4          27
103          0     5      5        10
应该怎么写，效率最高.多谢

Answer 1

 
 select  pid,count(decode(type,'a',1)),count(decode(type,'b',1)),count(decode(type,'c',1)),sum(amount)
   from tablea
 group by pid

Answer 2

select sum(decode(type,'a',amount,0)) typeA,sum(decode(type,'b',amount,0)) typeB,
sum(decode(type,'c',amount,0)) typeC,sum(amount) allAmount from temp group by pid

Answer 3

with temp as(
select 101 pid,'a' type,20 amount,'20010101' "date" from dual
union all
select 103 pid,'b' type,5 amount,'20010103' "date" from dual
union all
select 101 pid,'c' type,4 amount,'20010104' "date" from dual
union all
select 101 pid,'a' type,3 amount,'20010103' "date" from dual
union all
select 103 pid,'c' type,5 amount,'20010201' "date" from dual
)
select pid, sum(decode(type,'a',amount,0)) typeA,sum(decode(type,'b',amount,0)) typeB,
sum(decode(type,'c',amount,0)) typeC,sum(amount) allAmount from temp group by pid

Answer 4

select pid,sum(decode(type,'a',amount)) typea,
sum(decode(type,'b',amount)) typeb,
sum(decode(type,'c',amount)) typec,
sum(amount) allAmount
from tb
group by pid