求助，这条sql要怎么写好呢？

[aoxianglin]

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


一个表里，做不同类别的合计

[xdy3008]

........

[Headsen]

汗！楼主这SQL语句写的！
[Quote=引用楼主 aoxianglin 的回复:]
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 ……
[/Quote]
当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

[Headsen]

汗！忘了group by了。

[xiaohu8855]

给出测试数据和你要的结果，别人才可以给你看看

[luoyoumou]

select 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;

[luoyoumou]

select 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;

[aoxianglin]

[Quote=引用 1 楼 xiaohu8855 的回复:]
给出测试数据和你要的结果，别人才可以给你看看
[/Quote]
话说，俺也没数据，没办法啊

[xdy3008]

............

[xmanliming]

得有测试数据啊 。。。哈士奇挺可爱的。

[心中的彩虹]

[Quote=引用楼主 aoxianglin 的回复:]
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.……
[/Quote]

select open_brh_id,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,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