多表连接如何做 ？

[wfliu]

表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 30
3 70 90
4 20

请使用 JOIN 连接a,b,c 不要用子查询

[wfliu]

表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 30
3 70 90
4 20

[wfliu]

帮忙啊

[$扫地僧$]

create table A
(
id int ,

)
insert A
select 1 union
select 2 union
select 3 union
select 4

create table b
(
id int ,
zhichu int

)
insert B
select 1,10 union
select 3,20 union
select 4,20 union
select 3,50

create table C
(
id int ,
shouru int

)
insert C
select 2,10 union
select 3,20 union
select 3,20 union
select 1,60

select AB.id,AB.zhichu,isnull(C.shouru,'') as shouru from
(select A.id ,isnull(B.zhichu, '') as zhichu from A
LEFT OUTER JOIN (select id,sum(zhichu) as zhichu from B group by id) B
on A.id=B.id) AB LEFT OUTER JOIN (select id,sum(shouru) as shouru from C group by id) C on
AB.id=C.id

[wfliu]

scmail81(freedom) 大哥，能讲解一下吗?

[winehero]

--采用表的左连接
select a.id as id,
b.zhichu as zhichu,
c.shouru as shouru
from a, b, c
where a.id *= b.id and
a.id *= c.id

[lw1a2]

楼上的显然不对，帮顶

[wfliu]

等待啊

[lw1a2]

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

[qijl]

select a.id , sum(b.zhichu) , sum(c.shouru)
from a left join b on a.id=b.id
left join c on a.id=c.id
group by a.id

[$扫地僧$]

先在 表A与表B间用左连接连接！然后将这张得到的表在与 C表建立左连接！！

[zhaoanle]

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

[wfliu]

谢谢各位