表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
现在要写个查询，结果为，怎么写呢
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>