一个比较难的问题，表连接

[strongsun]

将三个表
table1
-----
部门
a
b
c
d
e
f
g


table2
---------------
部门 类别 数量
a z 1
e x 7
d y 2

table3

部门 类别 数量
a x 9

进行连接
要求链节后的结果为：

table
部门 x y z
--------------------------
a 9 0 1
b 0 0 0
c 0 0 0
d 0 2 0
e 7 0 0
f 0 0 0
g 0 0 0
-------------------------

[j9988]

select a.bm,
(case when b.lb='x' then sl else 0 end) as x,
(case when b.lb='y' then sl else 0 end) as y,
(case when b.lb='z' then sl else 0 end) as x
from table1 left join
(select bm,lb,sl from table2 union all
select bm,lb,sl from table3 ) as b on a.bm=b.bm

[j9988]

select a.bm,
sum(case when b.lb='x' then sl else 0 end) as x,
sum(case when b.lb='y' then sl else 0 end) as y,
sum(case when b.lb='z' then sl else 0 end) as x
from table1 left join
(select bm,lb,sl from table2 union all
select bm,lb,sl from table3 ) as b on a.bm=b.bm
group by bm

[j9988]

group by a.bm

[OpenVMS]

select 部门,isnull(sum(case when type='x' then num end),0) 'x',
isnull(sum(case when type='y' then num end),0) 'y',
isnull(sum(case when type='z' then num end),0) 'z'
(select a.部门,b.类别 type,b.数量+c.数量 num
from table1 a left join table2 b on a.部门=b.部门 left join table3 c on a.部门=c.部门) d
group by 部门

[OpenVMS]

select 部门,isnull(sum(case when type='x' then num end),0) 'x',
isnull(sum(case when type='y' then num end),0) 'y',
isnull(sum(case when type='z' then num end),0) 'z'
fromm (select a.部门,b.类别 type,b.数量+c.数量 num
from table1 a left join table2 b on a.部门=b.部门 left join table3 c on a.部门=c.部门) d
group by 部门

[OpenVMS]

可不要ISNULL
select 部门,sum(case when type='x' then num end) 'x',
(sum(case when type='y' then num end) 'y',
(sum(case when type='z' then num end) 'z'
FROM (select a.部门,b.类别 type,b.数量+c.数量 num
from table1 a left join table2 b on a.部门=b.部门 left join table3 c on a.部门=c.部门) d
group by 部门

[j9988]

select a.bm,
sum(case when b.lb='x' then sl else 0 end) as x,
sum(case when b.lb='y' then sl else 0 end) as y,
sum(case when b.lb='z' then sl else 0 end) as x
from table1 a left join
(select bm,lb,sl from table2 union all
select bm,lb,sl from table3 ) as b on a.bm=b.bm
group by a.bm

[hillhx]

2和3先UNION ALL 然后和1联合再GROUP BY 再SUM（CSAS WHEN X THEN X END）

[OpenVMS]

用UNION不如用JOIN,想想如果TABLE3为a z 9,那结果是不是有问题

[j9988]

UNION ALL 就不会了

[OpenVMS]

TABLE1 FULL JOIN就不会有问题

[OpenVMS]

TABLE1 FULL JOIN就不会有问题

[j9988]

to:OpenVMS(半知半解),有问题，因为type 你只选table2的

[j9988]

还有b.数量+c.数量　应改为：isnull(b.数量,0)+isnull(c.数量,0)

[OpenVMS]

1 我的写法确实有问题,JOIN后不好处理
全用JOIN的写法(没有必要)

select a.部门,
isnull(sum(case when b.类别='x' then b.数量 end),0)+isnull(sum(case when c.类别='x' then c.数量 end),0) 'x',
isnull(sum(case when b.类别='y' then b.数量 end),0)+isnull(sum(case when c.类别='y' then c.数量 end),0) 'y',
isnull(sum(case when b.类别='z' then b.数量 end),0)+isnull(sum(case when c.类别='z' then c.数量 end),0) 'z'
from table1 a left join table2 b on a.部门=b.部门 left join table3 c on a.部门=c.部门
group by a.部门

2 标准写法,如j9988

select a.部门,
ISNULL(sum(case when 类别='x' then 数量 end),0) 'x',
ISNULL(sum(case when 类别='y' then 数量 end),0) 'y',
ISNULL(sum(case when 类别='z' then 数量 end),0) 'z'
from table1 a left join (select * from table2 union all select * from table3 ) b on a.部门=b.部门
group by a.部门

3 子查询写法 1

select a.部门,
(select isnull(sum(数量),0) from table2 where 部门=a.部门 and 类别='x')+(select isnull(sum(数量),0) from table3 where 部门=a.部门 and 类别='x') 'x',
(select isnull(sum(数量),0) from table2 where 部门=a.部门 and 类别='y')+(select isnull(sum(数量),0) from table3 where 部门=a.部门 and 类别='y') 'y',
(select isnull(sum(数量),0) from table2 where 部门=a.部门 and 类别='z')+(select isnull(sum(数量),0) from table3 where 部门=a.部门 and 类别='z') 'z'
from table1 a

4 子查询写法 2

select a.部门,
(select isnull(sum(数量),0) from (select * from table2 union all select * from table3) b where 部门=a.部门 and 类别='x') 'x',
(select isnull(sum(数量),0) from (select * from table2 union all select * from table3) b where 部门=a.部门 and 类别='y') 'y',
(select isnull(sum(数量),0) from (select * from table2 union all select * from table3) b where 部门=a.部门 and 类别='z') 'z'
from table1 a

[strongsun]

谢谢 OpenVMS(半知半解)和 j9988(j9988）的完整的答案，还有 hillhx(曾经的曾经) 的参与，谢谢你们！！