Oracle SQL：创建标记账户ID在三张表中存在情况的标记表

需求说明 #

现有三张表（假设表名为table1、table2、table3，各含账户ID列account_id，列内ID可能重复），需生成一张标记表：包含所有唯一账户ID，并标记每个ID是否存在于对应表中（用1表示存在，0表示不存在，NULL也可接受）。

解决方案一：CTE + LEFT JOIN + CASE WHEN #

先通过UNION获取所有唯一账户ID，再分别左连接各表的去重ID，用CASE判断存在性：

WITH all_accounts AS (
    SELECT account_id FROM table1
    UNION
    SELECT account_id FROM table2
    UNION
    SELECT account_id FROM table3
)
SELECT
    aa.account_id AS "账户ID（UNION去重）",
    CASE WHEN t1.account_id IS NOT NULL THEN 1 ELSE 0 END AS "是否存在于表1？",
    CASE WHEN t2.account_id IS NOT NULL THEN 1 ELSE 0 END AS "是否存在于表2？",
    CASE WHEN t3.account_id IS NOT NULL THEN 1 ELSE 0 END AS "是否存在于表3？"
FROM all_accounts aa
LEFT JOIN (SELECT DISTINCT account_id FROM table1) t1 ON aa.account_id = t1.account_id
LEFT JOIN (SELECT DISTINCT account_id FROM table2) t2 ON aa.account_id = t2.account_id
LEFT JOIN (SELECT DISTINCT account_id FROM table3) t3 ON aa.account_id = t3.account_id
ORDER BY aa.account_id;

解决方案二：UNION ALL + 聚合函数 #

将三张表数据合并并标记来源，再通过分组聚合判断存在性：

SELECT
    account_id AS "账户ID（UNION去重）",
    MAX(CASE WHEN source = 'table1' THEN 1 ELSE 0 END) AS "是否存在于表1？",
    MAX(CASE WHEN source = 'table2' THEN 1 ELSE 0 END) AS "是否存在于表2？",
    MAX(CASE WHEN source = 'table3' THEN 1 ELSE 0 END) AS "是否存在于表3？"
FROM (
    SELECT account_id, 'table1' AS source FROM table1
    UNION ALL
    SELECT account_id, 'table2' AS source FROM table2
    UNION ALL
    SELECT account_id, 'table3' AS source FROM table3
) combined
GROUP BY account_id
ORDER BY account_id;

注意事项 #

• 请将代码中的table1、table2、table3和account_id替换为实际表名与列名；
• 若允许用NULL替代0表示不存在，可去掉CASE中的ELSE 0部分。

内容的提问来源于stack exchange，提问作者KevinR