表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
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
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
(
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,50create 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
go
create table #b(id int null,zhichu int null)
go
create table #c(id int null,shouru int null)
goinsert into #a values(1)
insert into #a values(2)
insert into #a values(3)
insert into #a values(4)
goinsert into #b values(1,10)
insert into #b values(3,20)
insert into #b values(4,20)
insert into #b values(3,50)
goinsert into #c values(2,30)
insert into #c values(3,40)
insert into #c values(3,50)
insert into #c values(1,60)
goselect 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
from a left join b on a.id=b.id
left join c on a.id=c.id
group by a.id
(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