表A1
id A
1 1
2 2
3 3
表A2
id B
2 22
3 33
5 55
表A3
id C
1 111
2 222
7 777
表A4
id D
5 5555
6 6666
7 7777
現(xiàn)在要寫個查詢，結(jié)果為，怎么寫呢
id A B C D
1 1 0 111 0
2 2 22 222 0
3 3 33 0 0
5 0 55 0 5555
6 0 0 0 6666
7 0 0 777 7777
select a5.id,COALESCE(a1."A",0) as A,COALESCE(a2."B",0) as b,
COALESCE(a3."C",0) as c,COALESCE(a4."D",0) as d from
(select distinct id as id from (select id from a1 union
select id from a2 union select id from a3
union select id from a4)as a6) as a5 left join a1 on a5.id = a1.id left join a2 on a5.id = a2.id left join a3 on a5.id = a3.id
left join a4 on a5.id = a4.id
</script>