select tm.open_brh_id,tm.orgname,
a.sa,b.sb,c.sc,d.sd,d.sd/2
from tm,(select tm.open_brh_id,sum(bal) sa from tm group by tm.open_brh_id) a,
(select tm.open_brh_id,sum(bal) sb from tm where tm.acc_belong='1' group by tm.open_brh_id) b,
(select tm.open_brh_id,sum(bal) sc from tm where tm.acc_belong='2' group by tm.open_brh_id) c,
(select tm.open_brh_id,sum(bal) sd from tm where tm.acc_belong='3' group by tm.open_brh_id) d
where tm.open_brh_id=a.open_brh_id
and tm.open_brh_id=b.open_brh_id
and tm.open_brh_id=c.open_brh_id
and tm.open_brh_id=d.open_brh_id
一个表里,做不同类别的合计
a.sa,b.sb,c.sc,d.sd,d.sd/2
from tm,(select tm.open_brh_id,sum(bal) sa from tm group by tm.open_brh_id) a,
(select tm.open_brh_id,sum(bal) sb from tm where tm.acc_belong='1' group by tm.open_brh_id) b,
(select tm.open_brh_id,sum(bal) sc from tm where tm.acc_belong='2' group by tm.open_brh_id) c,
(select tm.open_brh_id,sum(bal) sd from tm where tm.acc_belong='3' group by tm.open_brh_id) d
where tm.open_brh_id=a.open_brh_id
and tm.open_brh_id=b.open_brh_id
and tm.open_brh_id=c.open_brh_id
and tm.open_brh_id=d.open_brh_id
一个表里,做不同类别的合计
sum(bal) as sa,
sum(decode(acc_belong,'1',bal,0) as sb,
sum(decode(acc_belong,'2',bal,0) as sc,
sum(decode(acc_belong,'3',bal,0) as sd,
sum(decode(acc_belong,'3',bal,0)/2 as sd_2
from tm
group by tm.open_brh_id,tm.orgname;
sum(bal) as sa,
sum(decode(acc_belong,'1',bal,0)) as sb,
sum(decode(acc_belong,'2',bal,0)) as sc,
sum(decode(acc_belong,'3',bal,0)) as sd,
sum(decode(acc_belong,'3',bal,0))/2 as sd_2
from tm
group by tm.open_brh_id,tm.orgname;
sum(case when acc_belong='1' then bal end) sb,
sum(case when acc_belong='2' then bal end) sc,
sum(case when acc_belong='3' then bal end) sd,
cast((sum(case when acc_belong='3' then bal end)/2) as number(18,2)) sd2
from tm
group by open_brh_id,orgname
select open_brh_id,max(orgname),sum(bal) sa,
sum(case when acc_belong='1' then bal end) sb,
sum(case when acc_belong='2' then bal end) sc,
sum(case when acc_belong='3' then bal end) sd,
cast((sum(case when acc_belong='3' then bal end)/2) as number(18,2)) sd2
from tm
group by open_brh_id
--在你的改下
select open_brh_id,tm.orgname,sa=(select sum(bal) from tm where a.open_brh_id=open_brh_id group by open_brh_id),
sb=(select sum(bal) from tm where a.open_brh_id=open_brh_id and acc_belong='1' group by open_brh_id),
sc=(select sum(bal) from tm where a.open_brh_id=open_brh_id and acc_belong='2' group by open_brh_id),
sd=(select sum(bal) from tm where a.open_brh_id=open_brh_id and acc_belong='3' group by open_brh_id),
sd2=(select cast((sum(bal)/2) as number(18,2)) from tm where a.open_brh_id=open_brh_id and acc_belong='3'
group by open_brh_id)
from tm a
当SQL语句只返回一条记录时,可以作为select后面的字段,所以:select
open_brh_id
,(select sum(bal) from tm where tm.acc_belong='1' and open_brh_id=tm2.open_brh_id ) b
,(select sum(bal) from tm where tm.acc_belong='2' and open_brh_id=tm2.open_brh_id ) c
,(select sum(bal) from tm where tm.acc_belong='3' and open_brh_id=tm2.open_brh_id ) d
from tm tm2