您当前的位置: 比如
A表 有下列
name stock(库存)
苹果 10
香蕉 20
B表
name import(入库)
苹果 5
苹果 10
香蕉 20
C表
name delivery(出库)
苹果 5
最后查出的结果要这样
name(名称) stock(库存) import(入库) delivery(出库)
苹果 10 15 5
香蕉 20 20 0
请问这样的话sql语句要怎么写啊？
sql
多张表合并列
------解决方案--------------------
SELECT NAME,SUM(stock)stock,SUM(import) import,SUM(delivery) delivery
FROM (
SELECT a.NAME,SUM(ISNULL(stock,0)) stock,0 import,0 delivery
FROM a
GROUP BY NAME
UNION ALL
SELECT b.NAME,0 stock,SUM(ISNULL(import,0)) import,0 delivery
FROM b
GROUP BY NAME
UNION ALL
SELECT c.NAME,0 stock,0 import,SUM(ISNULL(delivery,0)) delivery
FROM c
GROUP BY NAME
)a
GROUP BY name
------解决方案--------------------
with pro_name as
(
select name from A表 union
select name from B表 union
select name from C表
)
select * from pro_name left join A on pro_name.name = A.name
left join B on pro_name.name = B.name
left join C on pro_name.name = C.name
------解决方案--------------------
select a.name,isnull(a.stock,0) stock,isnull(b.import,0) import,isnull(c.delivery,0) delivery from
(select name,sum(isnull(stock,0)) stock from a group by name) a left join
(select name,sum(isnull(import,0)) import from b group by name) b on a.name=b.name left join
(select name,sum(isnull(delivery,0)) delivery from c group by name) c on b.name=c.name