多表连接
表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
但是出现笛卡尔乘积,如果把单位加入连接条件的话就会漏掉数据
[解决办法]
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
[解决办法]
借用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数量单位
-------------------------------------
CPU1250公斤
CPU5100箱
MEMORY2500公斤
MEMORY0100箱
LCD0100公斤
LCD050箱
*/
