我正在尝试将 3 个表合并为一个，以收集有关emp_id员工的所有信息。问题是emp_id可能不存在另一个。所有emp_id都应该在一起，如果表中存在，则应写入值，否则应写入 0
exchange_records
--------------------------------------------------------------
id | emp_id | sum_exchange |
--------------------------------------------------------------
1 | 203100 | 2291520000 |
2 | 161938 | 197809000 |
3 | 230316 | 9583832300 |
4 | 193386 | 1793223000 |
swift_records
--------------------------------------------------------------
id | emp_id | sum_swift |
--------------------------------------------------------------
1 | 203100 | 39596400 |
2 | 171831 | 10136600 |
3 | 310531 | 707708.51 |
4 | 193386 | 38117100 |
5 | 183003 | 105363.05 |
spot_records
--------------------------------------------------------------
id | emp_id | sum_spot |
--------------------------------------------------------------
1 | 233076 | 39596400 |
2 | 171831 | 10136600 |
3 | 200172 | 707708.51 |
4 | 163389 | 38117100 |
结果应如下所示
-----------------------------------------
id | emp_id | sum_exchange | sum_swift | sum_spot |
----------------------------------------------------------------------------
1 | 203100 | 2291520000 | 39596400 | 0 |
1 | 161938 | 197809000 | 0 | 0 |
1 | 230316 | 9583832300 | 0 | 0 |
1 | 193386 | 1793223000 | 38117100 | 0 |
1 | 171831 | 0 | 10136600 | 10136600 |
1 | 310531 | 0 | 707708.51 | 0 |
1 | 183003 | 2291520000 | 105363.05 | 0 |
1 | 203100 | 2291520000 | 0 | 0 |
1 | 233076 | 0 | 0 | 39596400 |
1 | 200172 | 0 | 0 | 707708.51 |
1 | 163389 | 0 | 0 | 38117100 |
您正在寻找full join：
select
emp_id,
coalesce(e.sum_exchange, 0) sum_exchange,
coalesce(w.sum_swift, 0) sum_swift,
coalesce(s.sum_spot, 0) sum_spot
from
exchange_records e
full join swift_records w using(emp_id)
full join spot_records s using(emp_id)
DB小提琴上的演示：
emp_id | sum_exchange | sum_swift | sum_spot -----: |-----------: |----------: |----------: 161938 | 197809000 | 0 | 0 163389 | 0 | 0 |38117100.00 171831 | 0 |10136600.00 |10136600.00 183003 | 0 | 105363.05 | 0 193386 | 1793223000 |38117100.00 | 0 200172 | 0 | 0 | 707708.51 203100 | 2291520000 |39596400.00 | 0 230316 | 9583832300 | 0 | 0 233076 | 0 | 0 |39596400.00 310531 | 0 | 707708.51 | 0