select id,name,sum(amount) as amount from (
select id,name,amount from a
union all
select id,name,-amount from b
union all
select id,name,-amount from c)T
group by id,name
select id,name,amount from a
union all
select id,name,-amount from b
union all
select id,name,-amount from c)T
group by id,name
create table a(id int,name varchar(10),amount int)
insert a
select 1,'牙刷',30 union all
select 2,'牙膏',40 union all
select 3,'肥皂',50
create table b(id int,name varchar(10),amount int)
insert b
select 1,'牙刷',10 union all
select 2,'牙膏',10
create table c(id int,name varchar(10),amount int)
insert c
select 1,'牙刷',10 select a.id,
a.name,
a.amount-isnull(b.amount,0)-isnull(c.amount,0)
from a
left join b
on a.name=b.name
left join c
on a.name=c.name/*
1 牙刷 10
2 牙膏 30
3 肥皂 50
*/
select aa.id,
aa.name,
aa.amount-isnull(bb.amount,0)-isnull(cc.amount,0)
from (select
id,
name,
amount=sum(amount)
from a
group by id,
name
) aa
left join (select
id,
name,
amount=sum(amount)
from b
group by id,
name
)bb
on aa.name=bb.name
left join (select id,
name,
amount=sum(amount)
from c
group by id,
name
) cc
on aa.name=cc.name
select a.id,
a.name,
a.amount-isnull(SUM(b.amount),0)-isnull(SUM(c.amount),0) ---如果每种商品只入库一次(当然一般不可能)
from #a AS A
left join #b AS B
on a.name=b.name
left join #c AS c
on a.name=c.NAME
GROUP BY a.id,
a.name
,a.amount
ORDER BY a.idSELECT a.name,a.recvQty-ISNULL(b.suppQty,0)-ISNULL(c.suppQty,0) --库存
FROM (
SELECT name,SUM(amount) AS recvQty --每种商品入库记录数量和
FROM #a
GROUP BY name
) a LEFT JOIN (
SELECT name,SUM(amount) AS suppQty --每种商品借出记录数量和
FROM #b
GROUP BY name
) b ON A.name=b.name
LEFT JOIN(
SELECT name,SUM(amount) AS suppQty --每种商品出库记录数量和
FROM #c
GROUP BY name) c ON a.name=c.name
ORDER BY a.name DESC