sql 多张表的列合并成一条数据

[qf19881213]

比如
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语句要怎么写啊？

[發糞塗牆]

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

[yiyishuitian]

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 

[qf19881213]

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 1楼的版主啊，这样写的话，会查询出多条数据 并不是一条啊

[qf19881213]

2楼的也不对啊，这样写的话也是查出来有多条的啊 我要的是一种水果一条数据啊，把他们的库存，入库，出库数量是加起来的

[發糞塗牆]

没问题啊

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-01-14 14:04:49
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据：[A表]
if object_id('[A表]') is not null drop table [A表]
go 
create table [A表]([name] varchar(4),[stock(库存)] int)
insert [A表]
select '苹果',10 union all
select '香蕉',20

--> 测试数据：[B表]
if object_id('[B表]') is not null drop table [B表]
go 
create table [B表]([name] varchar(4),[import(入库)] int)
insert [B表]
select '苹果',5 union all
select '苹果',10 union all
select '香蕉',20

--> 测试数据：[C表]
if object_id('[C表]') is not null drop table [C表]
go 
create table [C表]([name] varchar(4),[delivery(出库)] int)
insert [C表]
select '苹果',5
--------------开始查询--------------------------

select * from [C表]
--------------开始查询--------------------------

select * from [B表]
--------------开始查询--------------------------

select * from [A表]
----------------结果----------------------------

SELECT NAME,SUM(stock)stock,SUM(import) import,SUM(delivery) delivery 
FROM ( 
SELECT NAME,SUM(ISNULL([stock(库存)],0)) stock,0 import,0 delivery 
FROM [A表] 
GROUP BY NAME
UNION ALL 
SELECT NAME,0 stock,SUM(ISNULL([import(入库)],0)) import,0 delivery 
FROM [B表] 
GROUP BY NAME
UNION ALL 
SELECT NAME,0 stock,0 import,SUM(ISNULL([delivery(出库)],0)) delivery 
FROM [c表] 
GROUP BY NAME
)a 
GROUP BY name
/* 
NAME stock       import      delivery
---- ----------- ----------- -----------
苹果   10          15          5
香蕉   20          20          0

(2 行受影响)

*/

[發糞塗牆]

如果不对，把你原有的数据尽可能多的贴出来看看。

[小猴168]

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

[着魔]

目测5楼的是对的

[mintian7]

实践证明5楼的是对的

[小孩快跑]

引用 5 楼 DBA_Huangzj 的回复:

没问题啊SQL code?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768-----------------------------------------------……

测试过，是正确的

[qf19881213]

恩，谢谢你们，解决啦