sql join语句询问

[tsaotony]

如下，有4个表，如何形成E表
a表
id num
1 3
2 7
b表
id num1
1 3
3 5
4 6
c表
id num2
1 7
4 12
d表
id num3
5 23
6 32
E表
id num num1 num2 num3
1 3 3 7 0
2 7 0 0 0
3 0 5 0 0
4 0 6 17 0
5 0 0 0 23
6 0 0 0 32

[老猫五号]

select A.Id,case when A.num is null then 0 else A.num end num,
case when B.num1 is null then 0 else B.num1 end num1,
case when C.num2 is null then 0 else B.num2 end num2,
case when D.num3 is null then 0 else B.num3 end num3
from A out join B on A.id = B.id
out join C on C.id = A.id
out join D on D.id = A.id

[老猫五号]

select A.Id,case when A.num is null then 0 else A.num end num,
case when B.num1 is null then 0 else B.num1 end num1,
case when C.num2 is null then 0 else C.num2 end num2,
case when D.num3 is null then 0 else D.num3 end num3
from A out join B on A.id = B.id
out join C on C.id = A.id
out join D on D.id = A.id

[tsaotony]

out join好像不行

[tsaotony]

select A.Id。。。
E表里包含了4个表的id

[poppan007]

我是2k,out有错误,不能被解析,揣摩2楼和3楼的意图之后,将out改为full,依然出错- -

[老猫五号]

看来我要出绝招了
select A.Id,A.num,0 num1,0 num2,0 num3 from A
union all
select B.Id,0,num1,0,0 from B
union all
select C.Id,0,0,num2,0 from C
union all
select D.Id,0,0,0,num3 from D

[tsaotony]

full可以但是不是我要的结果

[老猫五号]

先前没看清楚，6楼应该就是答案了

[老猫五号]

最多再加个排序
select * from
(
select A.Id,A.num,0 num1,0 num2,0 num3 from A
union all
select B.Id,0,num1,0,0 from B
union all
select C.Id,0,0,num2,0 from C
union all
select D.Id,0,0,0,num3 from D

) E order by E.id

[tsaotony]

union all不行的

[老猫五号]

为什么union all不行呢？

[tsaotony]

b表
id num1
1 3
3 5
4 6
c表
id num2
1 7
4 12

id 4在num1和num2里都有的

[AcHerat]

select t.id,isnull(a.num,0) num,isnull(b.num1,0) num1,isnull(c.num2,0) num2,isnull(d.num3,0) num3
from(
    select id from a
    union
    select id from b
    union
    select id from c
    union
    select id from d
)t left join a on t.id = a.id
   left join b on t.id = b.id
   left join c on t.id = c.id
   left join d on t.id = d.id

[tsaotony]

union all的话id就有重复了

[老猫五号]

select id,max(num) as num,max(num1) num1,max(num2) num2,max(num3) num3 from
(
select A.Id,A.num,0 num1,0 num2,0 num3 from A
union all
select B.Id,0,num1,0,0 from B
union all
select C.Id,0,0,num2,0 from C
union all
select D.Id,0,0,0,num3 from D

) E
group by A.id
order by E.id

[老猫五号]

select id,max(num) as num,max(num1) num1,max(num2) num2,max(num3) num3 from
(
select A.Id,A.num,0 num1,0 num2,0 num3 from A
union all
select B.Id,0,num1,0,0 from B
union all
select C.Id,0,0,num2,0 from C
union all
select D.Id,0,0,0,num3 from D

) E
group by E.id
order by E.id

[poppan007]

经测试,13楼为正解- -

[poppan007]

/*
1 3 3 7 0
2 7 0 0 0
3 0 5 0 0
4 0 6 12 0
5 0 0 0 23
6 0 0 0 32
*/