select id,sum(isnull(volume,0)) as volume
from(
select id,volume from a
union all
select id,0-volume from b
union all
select id,volume from c
)t
group by id
order by id
from(
select id,volume from a
union all
select id,0-volume from b
union all
select id,volume from c
)t
group by id
order by id
from a
left join b
on a.id=b.id
left join c
on a.id=c.id
from(
select a.id as id,a.volume-isnull(b.volume,0)+isnull(c.volume,0) as volume
from a
left join b
on a.id=b.id
left join c
on a.id=c.id
)t
group by id
order by id
from a表 a
left join (select ID,销售量=sum(Volume) from b表 group by ID) b on a.ID=b.ID
left join (select ID,进货量=sum(Volume) from c表 group by ID) c on a.ID=c.ID
create table a表(id int,Volume int)
create table b表(id int,volume int)
create table c表(id int,volume int)
insert a表
select 111, 2
union all select 222, 9
union all select 333, 8
union all select 444, 12
union all select 555, 10
insert b表
select 111, 4
union all select 111, 9
union all select 333, 7
insert c表
select 333, 8
union all select 444, 7
union all select 444, 8
--查询
select a.ID,Volume=a.Volume+isnull(c.进货量,0)-isnull(b.销售量,0)
from a表 a
left join (select ID,销售量=sum(Volume) from b表 group by ID) b on a.ID=b.ID
left join (select ID,进货量=sum(Volume) from c表 group by ID) c on a.ID=c.ID--结果
/*
ID Volume
----------- -----------
111 -11
222 9
333 9
444 27
555 10(所影响的行数为 5 行)
*/
create table a表(id int,Volume int)
create table b表(id int,volume int)
create table c表(id int,volume int)
insert a表
select 111, 2
union all select 222, 9
union all select 333, 8
union all select 444, 12
union all select 555, 10
insert b表
select 111, 4
union all select 111, 9
union all select 333, 7
insert c表
select 333, 8
union all select 444, 7
union all select 444, 8select id,sum(volume) as volume
from(
select id,volume from a表
union all
select id,0-volume from b表
union all
select id,volume from c表
)t
group by id
order by iddrop table a表
drop table b表
drop table c表
from a
left join b on a.ID=b.id group by ID
left join c on a.ID=c.id group by ID