多表连接

Question

表A
ID (主键) 名称
1         CPU
2         MEMORY
3         LCD
4         MOUSE表B
ID       B数量     单位
1        12       公斤
1        5        箱
2        10       公斤
2        15       公斤表C
ID       C数量     单位
1        5        公斤
3        10       公斤表D
ID       D数量     单位
1        10       箱
2        10       箱
3        5        箱要把四个表连接起来并按单位加总达到下面结果
ID    名称    B数量   C数量   D数量  单位
===== ======= ======= ======= ====== ====
1     CPU     12      5       0      公斤
1     CPU     5       0       10     箱
2     MEMORY  25      0       0      公斤
2     MEMORY  0       0       10     箱
3     LCD     0       10      0      公斤
3     LCD     0       0       5      箱我这样写
select a.*,x.B数量,y.C数量,z.D数量,x.单位 from a
left join (select id,sum(B数量),单位 from b group by id,单位) x on a.id=x.id
left join (select id,sum(C数量),单位 from c group by id,单位) y on a.id=y.id
left join (select id,sum(D数量),单位 from d group by id,单位) z on a.id=z.id
但是出现笛卡尔乘积,如果把单位加入连接条件的话就会漏掉数据

Answer 1

单位应该建在表A吧？
你这CPU 又是公斤 又是 箱的，看了好乱!

Answer 2

我这只是举个例子,不是真正的CPU,我的语句写了有问题,应该是full join才对,
就是说应该先把B,C,D三个表full join连接起来,然后才把A表中的名称加入结果中,但是full join如果B中有而C中没有的话就会有一个NULL值

Answer 3

这么说吧,B是上月结存,C是本月入库,D是现库存,如何返回ID   名称   上月结存   本月入库   现库存这样的形式?

Answer 4

declare @a table(ID int, 名称 varchar(20))
insert @a select 1 ,'CPU'
union all select 2 ,'MEMORY'
union all select 3 ,'LCD'
union all select 4 ,'MOUSE'declare @B table(ID int, B数量 int, 单位  varchar(20))
insert @b select 1 ,12 ,'公斤'
union all select 1 ,5 ,'箱'
union all select 2, 10 ,'公斤'
union all select 2 ,15 ,'公斤'declare @C table(ID int, C数量 int,单位  varchar(20))
insert @c select 1 ,5 ,'公斤'
union all select 3, 10 ,'公斤'declare @D table( ID int, D数量 int, 单位  varchar(20))
insert @d select 1 ,10 ,'箱'
union all select 2 ,10 ,'箱'
union all select 3 ,5, '箱'select a.id,名称,isnull(b数量,0) b数量,isnull(c数量,0) c数量,isnull(d数量,0) d数量,单位 from @a a Left join
(select coalesce(j.id,z.id) id,b数量,c数量,d数量,coalesce(j.单位,z.单位) 单位 from(
select coalesce(x.id,y.id) id,b数量,c数量,coalesce(x.单位,y.单位) 单位 from (select id,sum(B数量) b数量,单位 from @b b group by id,单位 ) x full join 
(select id,sum(C数量) c数量,单位 from @c c group by id,单位) y on x.id=y.id and x.单位=y.单位) j
full join (select id,sum(D数量) d数量,单位 from @d d group by id,单位) z on j.id=z.id and j.单位=z.单位) n
On a.id=n.id

Answer 5

--resultid         名称               b数量         c数量         d数量         单位                   
----------- --------------- ----------- ----------- ----------- -------------- 
1           CPU             12          5           0           公斤
1           CPU             5           0           10          箱
2           MEMORY          25          0           0           公斤
2           MEMORY          0           0           10          箱
3           LCD             0           10          0           公斤
3           LCD             0           0           5           箱
4           MOUSE                0           0           0           NULL

Answer 6

单位与id应该是对应的　你的数据有问题
你把sum(D数量)后加别名，在select 调用别名试试

Answer 7

一条SQL语句,可以实现：
SELECT  A.[名称]
,MAX(CASE B.BillType WHEN 1 THEN B.数量 ELSE 0 END) AS [B数量]
,MAX(CASE B.BillType WHEN 1 THEN B.数量 ELSE 0 END) AS [C数量]
,MAX(CASE B.BillType WHEN 1 THEN B.数量 ELSE 0 END) AS [D数量]  
,B.[单位]
FROM 表A AS A LEFT OUTER JOIN 
(SELECT [id],sum(B数量) AS [数量],[单位],1 AS BillType FROM 表B GROUP BY [id],单位
UNION ALL SELECT [id],sum(C数量),[单位],2  FROM 表D GROUP BY [id],单位
UNION ALL SELECT [id],sum(D数量),[单位],2  FROM 表D GROUP BY [id],单位
) AS B ON A.[id]=B.[id]
GROUP BY A.[id],A.[名称]
,B.[单位]

Answer 8

不好意思，写错了几个123数字：
SELECT  A.[名称]
,MAX(CASE B.BillType WHEN 1 THEN B.数量 ELSE 0 END) AS [B数量]
,MAX(CASE B.BillType WHEN 2 THEN B.数量 ELSE 0 END) AS [C数量]
,MAX(CASE B.BillType WHEN 3 THEN B.数量 ELSE 0 END) AS [D数量]  
,B.[单位]
FROM 表A AS A LEFT OUTER JOIN 
(SELECT [id],sum(B数量) AS [数量],[单位],1 AS BillType FROM 表B GROUP BY [id],[单位]
UNION ALL SELECT [id],sum(C数量),[单位],2  FROM 表D GROUP BY [id],[单位]
UNION ALL SELECT [id],sum(D数量),[单位],2  FROM 表D GROUP BY [id],[单位]
) AS B ON A.[id]=B.[id]
GROUP BY A.[id],A.[名称]
,B.[单位]

Answer 9

create table A (ID int, 名称 varchar(20))
insert A select 1 ,'CPU'
union all select 2 ,'MEMORY'
union all select 3 ,'LCD'
union all select 4 ,'MOUSE'create table B (ID int, B数量 int, 单位  varchar(20))
insert b select 1 ,12 ,'公斤'
union all select 1 ,5 ,'箱'
union all select 2, 10 ,'公斤'
union all select 2 ,15 ,'公斤'create table C (ID int, C数量 int,单位  varchar(20))
insert c select 1 ,5 ,'公斤'
union all select 3, 10 ,'公斤'create table D ( ID int, D数量 int, 单位  varchar(20))
insert d select 1 ,10 ,'箱'
union all select 2 ,10 ,'箱'
union all select 3 ,5, '箱'select
A.[ID],
A.名称,
ISNULL(B.B数量,0) as B数量, 
ISNULL(C.C数量,0) as C数量, 
ISNULL(D.D数量,0) as D数量, 
ISNULL(case when B.单位 is not null then B.单位 
            when C.单位 is not null then C.单位 
            when D.单位 is not null then D.单位 end, '') as 单位
from A 
left join B on B.ID=A.ID
left JOIN C ON C.ID=A.ID
left JOIN D ON D.ID=A.IDdrop table A
drop table B
drop table C
drop table D---------------------------------------------
1 CPU 12 5 10 公斤
1 CPU 5 5 10 箱
2 MEMORY 10 0 10 公斤
2 MEMORY 15 0 10 公斤
3 LCD 0 10 5 公斤
4 MOUSE 0 0 0

Answer 10

crazyflower(小狂花，学习中……) 的有些问题,MEMORY的两个都是"公斤",没有加总,而且漏了"箱"

Answer 11

借用crazyflower(小狂花，学习中……) ( ) 的表结构，测试我的方法：
create table A (ID int, 名称 varchar(20))
insert A select 1 ,'CPU'
union all select 2 ,'MEMORY'
union all select 3 ,'LCD'
union all select 4 ,'MOUSE'create table B (ID int, B数量 int, 单位  varchar(20))
insert b select 1 ,12 ,'公斤'
union all select 1 ,5 ,'箱'
union all select 2, 10 ,'公斤'
union all select 2 ,15 ,'公斤'create table C (ID int, C数量 int,单位  varchar(20))
insert c select 1 ,5 ,'公斤'
union all select 3, 10 ,'公斤'create table D ( ID int, D数量 int, 单位  varchar(20))
insert d select 1 ,10 ,'箱'
union all select 2 ,10 ,'箱'
union all select 3 ,5, '箱'SELECT  A.[名称]
,MAX(CASE B.BillType WHEN 1 THEN B.数量 ELSE 0 END) AS [B数量]
,MAX(CASE B.BillType WHEN 2 THEN B.数量 ELSE 0 END) AS [C数量]
,MAX(CASE B.BillType WHEN 3 THEN B.数量 ELSE 0 END) AS [D数量]  
,B.[单位]
FROM A AS A INNER  JOIN 
(SELECT [id],sum(B数量) AS [数量],[单位],1 AS BillType FROM B GROUP BY [id],[单位]
UNION ALL SELECT [id],sum(C数量),[单位],2  FROM C GROUP BY [id],[单位]
UNION ALL SELECT [id],sum(D数量),[单位],2  FROM D GROUP BY [id],[单位]
) AS B ON A.[id]=B.[id]
GROUP BY A.[id],A.[名称]
,B.[单位]
drop table A
drop table B
drop table C
drop table D/*
名称 B数量 C数量 D数量 单位
-------------------------------------
CPU 12 5 0 公斤
CPU 5 10 0 箱
MEMORY 25 0 0 公斤
MEMORY 0 10 0 箱
LCD 0 10 0 公斤
LCD 0 5 0 箱
*/

Answer 12

错了一个3字，结果就不一样，不好意思,应该是:
SELECT  A.[名称]
,MAX(CASE B.BillType WHEN 1 THEN B.数量 ELSE 0 END) AS [B数量]
,MAX(CASE B.BillType WHEN 2 THEN B.数量 ELSE 0 END) AS [C数量]
,MAX(CASE B.BillType WHEN 3 THEN B.数量 ELSE 0 END) AS [D数量]  
,B.[单位]
FROM A AS A INNER  JOIN 
(SELECT [id],sum(B数量) AS [数量],[单位],1 AS BillType FROM B GROUP BY [id],[单位]
UNION ALL SELECT [id],sum(C数量),[单位],2  FROM C GROUP BY [id],[单位]
UNION ALL SELECT [id],sum(D数量),[单位],3  FROM D GROUP BY [id],[单位]
) AS B ON A.[id]=B.[id]
GROUP BY A.[id],A.[名称]
,B.[单位]
/*
名称 B数量 C数量 D数量 单位
-------------------------------------
CPU 12 5 0 公斤
CPU 5 0 10 箱
MEMORY 25 0 0 公斤
MEMORY 0 0 10 箱
LCD 0 10 0 公斤
LCD 0 0 5 箱
*/

Answer 13

楼上的是很巧妙的嘛,很值得参考,不过我这里只是举例子,事实上B,C,D三表数量的个数不同.要用0来补充其他表不包含的字段了.