还是用分析函数:select id,0 as lb0,1 as lb1,2 as lb2,3 as lb3,
max(decode(lb,0,s,0)) as value0,max(decode(lb,1,s,0)) as value1,
max(decode(lb,2,s,0)) as value2,max(decode(lb,3,s,0)) as value3 from(
select id,lb,sum(value) over (partition by id,lb) as s from a) group by id
max(decode(lb,0,s,0)) as value0,max(decode(lb,1,s,0)) as value1,
max(decode(lb,2,s,0)) as value2,max(decode(lb,3,s,0)) as value3 from(
select id,lb,sum(value) over (partition by id,lb) as s from a) group by id
期待着你的回信!呵呵,我马上结帖!
from
(select a.id,a.lb lb0,sum(a.value) value0
from a
where lb = 0
group by a.id,a.lb) t0,
(select a.id,a.lb lb1,sum(a.value) value1
from a
where lb = 1
group by a.id,a.lb ) t1,
(select a.id,a.lb lb2,sum(a.value) value2
from a
where lb = 2
group by a.id,a.lb ) t2,
(select a.id,a.lb lb3,sum(a.value) value3
from a
where lb = 3
group by a.id,a.lb ) t3
where t0.id = t1.id(+)
and t0.id = t2.id(+)
and t0.id = t3.id(+)