多表连接查询

数据库：MSSQL

表a(id),b(id,zhichu),c(id,shouru)
 a:        b:            c:
 1         1   10        2   30
 2         3   20        3   40
 3         4   20        3   50
 4         3   50        1   60
如何得到:
 id   zhichu    shouru
 1    10           60
 2    0             30
 3    70           90
 4    20           0

 

－－－－－－－－－－－－－－－－－－

create table #a(id int null)
go
create table #b(id int null,zhichu int null)
go
create table #c(id int null,shouru int null)
go

insert into #a values(1)
insert into #a values(2)
insert into #a values(3)
insert into #a values(4)
go

insert into #b values(1,10)
insert into #b values(3,20)
insert into #b values(4,20)
insert into #b values(3,50)
go

insert into #c values(2,30)
insert into #c values(3,40)
insert into #c values(3,50)
insert into #c values(1,60)
go

 

select d.id,d.zhichu,e.shouru
from
( select #a.id as id,
sum(isnull(zhichu,0)) as zhichu
from
#a left join #b on #a.id=#b.id
group by #a.id ) d,
(select #a.id as id,
sum(isnull(shouru,0)) as shouru
from
#a left join #c on #a.id=#c.id
group by #a.id ) e
where d.id=e.id

－－－－－－－－－－－－－－－－－－－－－