表 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
应该怎么写,效率最高.多谢
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
应该怎么写,效率最高.多谢
select pid,count(decode(type,'a',1)),count(decode(type,'b',1)),count(decode(type,'c',1)),sum(amount)
from tablea
group by pid
sum(decode(type,'c',amount,0)) typeC,sum(amount) allAmount from temp group by pid
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
sum(decode(type,'b',amount)) typeb,
sum(decode(type,'c',amount)) typec,
sum(amount) allAmount
from tb
group by pid