我正在对同一列上的 4 个表进行完全外连接。
我想为连接列中的每个不同值仅生成 1 行。
输入是:
employee1
+---------------------+-----------------+--+
| employee1.personid | employee1.name |
+---------------------+-----------------+--+
| 111 | aaa |
| 222 | bbb |
| 333 | ccc |
+---------------------+-----------------+--+
employee2
+---------------------+----------------+--+
| employee2.personid | employee2.sal |
+---------------------+----------------+--+
| 111 | 2 |
| 200 | 3 |
+---------------------+----------------+--+
employee3
+---------------------+------------------+--+
| employee3.personid | employee3.place |
+---------------------+------------------+--+
| 111 | bbsr |
| 300 | atl |
| 200 | ny |
+---------------------+------------------+--+
employee4
+---------------------+---------------+--+
| employee4.personid | employee4.dt |
+---------------------+---------------+--+
| 111 | 2019-02-21 |
| 300 | 2019-03-18 |
| 400 | 2019-03-18 |
+---------------------+---------------+--+
预期结果
每个 personid 一条记录,所以总共应该有 6 条记录(111,222,333,200,300,400)
喜欢:
+-----------+---------+--------+----------+-------------+--+
| personid | f.name | u.sal | v.place | v_in.dt |
+-----------+---------+--------+----------+-------------+--+
| 111 | aaa | 2 | bbsr | 2019-02-21 |
| 200 | NULL | 3 | ny | NULL |
| 222 | bbb | NULL | NULL | NULL |
| 300 | NULL | NULL | atl | 2019-03-18 |
| 333 | ccc | NULL | NULL | NULL |
| 400 | NULL | NULL | NULL | 2019-03-18 |
+-----------+---------+--------+----------+-------------+--+
我得到的结果是:
+-----------+---------+--------+----------+-------------+--+
| personid | f.name | u.sal | v.place | v_in.dt |
+-----------+---------+--------+----------+-------------+--+
| 111 | aaa | 2 | bbsr | 2019-02-21 |
| 200 | NULL | 3 | NULL | NULL |
| 200 | NULL | NULL | ny | NULL |
| 222 | bbb | NULL | NULL | NULL |
| 300 | NULL | NULL | atl | NULL |
| 300 | NULL | NULL | NULL | 2019-03-18 |
| 333 | ccc | NULL | NULL | NULL |
| 400 | NULL | NULL | NULL | 2019-03-18 |
+-----------+---------+--------+----------+-------------+--+
使用的查询:
select coalesce(f.personid, u.personid, v.personid, v_in.personid) as personid,f.name,u.sal,v.place,v_in.dt
from employee1 f FULL OUTER JOIN employee2 u on f.personid=u.personid
FULL OUTER JOIN employee3 v on f.personid=v.personid
FULL OUTER JOIN employee4 v_in on f.personid=v_in.personid;
请建议如何生成预期结果。