我有三個不同的表，看起來像這樣：
表格1
| id | city|
|----|-----|
| 1 | A |
| 1 | B |
| 2 | C |
表 2
| id | city|
|----|-----|
| 2 | B |
| 1 | B |
| 3 | C |
表3
| id | city|
|----|-----|
| 1 | A |
| 1 | B |
| 2 | A |
我需要為每個表創建一列，如果存在虛擬值，則需要創建一列。
| id | city| is_tbl_1 | is_tbl_2 | is_tbl_3 |
|----|-----|-----------|-------------|------------|
| 1 | A | 1 | 0 | 1 |
| 1 | B | 1 | 1 | 1 |
| 2 | A | 0 | 0 | 1 |
| 2 | C | 1 | 0 | 0 |
| 2 | B | 0 | 1 | 0 |
| 3 | C | 0 | 1 | 0 |
我試圖在三個不同的選擇中添加列 is_tbl# 自己，UNION 所有三個表和組，但它看起來很難看，有沒有更好的方法來做到這一點？
uj5u.com熱心網友回復：
您可以在 id 和 city 上外部連接 3 個表，然后按 id 和 city 分組，最后計算 city 列的非空值的數量：
SELECT
COALESCE (t1.id, t2.id, t3.id) AS id
, COALESCE (t1.city, t2.city, t3.city) AS city
, count(*) FILTER (WHERE t1.city IS NOT NULL) AS is_tbl_1
, count(*) FILTER (WHERE t2.city IS NOT NULL) AS is_tbl_2
, count(*) FILTER (WHERE t3.city IS NOT NULL) AS is_tbl_3
FROM
t1 AS t1
FULL OUTER JOIN
t2 AS t2 ON t1.id = t2.id AND t1.city = t2.city
FULL OUTER JOIN
t3 AS t3 ON t1.id = t3.id AND t1.city = t3.city
GROUP BY
1,2
ORDER BY
1,2
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/366110.html