如何在JOIN操作中获取指定非重复行？多表关联优先级去重需求

问题场景 #

执行多表JOIN时，因单条tab1记录匹配多条tab2记录导致结果出现重复行。需求为：优先保留Column B2 = 'PW'的记录；若该值不存在，则保留Column B2 = 'RW'的记录。

表结构与数据 #

tab1表

tab2表

当前查询语句 #

SELECT t1.column_A1, t2.column_B2 
FROM tab1 t1
JOIN tab2 t2
ON t1.column_A1 = t2.column_A2 

当前问题 #

查询结果出现重复行（同一Column A1对应多条tab2记录），需筛选出优先级最高的单条记录。

解决方案 #

方法1：窗口函数（推荐，适用于支持窗口函数的数据库） #

利用ROW_NUMBER()按分组标记优先级，仅保留每组中优先级最高的行，兼容MySQL 8+、PostgreSQL、SQL Server等主流数据库。

SELECT column_A1, column_B2, column_C2
FROM (
    SELECT 
        t1.column_A1, 
        t2.column_B2, 
        t2.column_C2,
        ROW_NUMBER() OVER (
            PARTITION BY t1.column_A1 
            ORDER BY CASE WHEN t2.column_B2 = 'PW' THEN 1 ELSE 2 END
        ) AS rn
    FROM tab1 t1
    JOIN tab2 t2 ON t1.column_A1 = t2.column_A2
) AS temp
WHERE rn = 1;

逻辑说明：

• PARTITION BY t1.column_A1：按tab1的Column A1分组，确保每个分组内处理匹配的tab2记录
• ORDER BY CASE...：给PW分配更高优先级（排序值更小），RW次之
• 外层查询筛选rn=1，即每个分组中优先级最高的行

方法2：子查询判断存在性（兼容低版本数据库） #

通过两次左连接分别筛选PW和RW记录，优先取PW数据，兼容不支持窗口函数的低版本数据库。

SELECT 
    t1.column_A1,
    COALESCE(t2_pw.column_B2, t2_rw.column_B2) AS column_B2,
    COALESCE(t2_pw.column_C2, t2_rw.column_C2) AS column_C2
FROM tab1 t1
LEFT JOIN tab2 t2_pw 
    ON t1.column_A1 = t2_pw.column_A2 AND t2_pw.column_B2 = 'PW'
LEFT JOIN tab2 t2_rw 
    ON t1.column_A1 = t2_rw.column_A2 AND t2_rw.column_B2 = 'RW'
WHERE t2_pw.column_A2 IS NOT NULL OR t2_rw.column_A2 IS NOT NULL;

逻辑说明：

• 分别左连接tab2筛选PW和RW的记录
• 用COALESCE优先取PW的字段值，若PW不存在则取RW的
• WHERE子句确保只保留有匹配记录的行（与原JOIN逻辑一致）

方法3：条件聚合（适合字段较少的场景） #

通过条件聚合和UNION ALL拆分两种场景，分别提取有PW和无PW的记录。

SELECT 
    t1.column_A1,
    MAX(CASE WHEN t2.column_B2 = 'PW' THEN t2.column_B2 ELSE t2.column_B2 END) AS column_B2,
    MAX(CASE WHEN t2.column_B2 = 'PW' THEN t2.column_C2 ELSE t2.column_C2 END) AS column_C2
FROM tab1 t1
JOIN tab2 t2 ON t1.column_A1 = t2.column_A2
GROUP BY t1.column_A1
HAVING MAX(CASE WHEN t2.column_B2 = 'PW' THEN 1 ELSE 0 END) = 1
UNION ALL
SELECT 
    t1.column_A1,
    t2.column_B2,
    t2.column_C2
FROM tab1 t1
JOIN tab2 t2 ON t1.column_A1 = t2.column_A2
WHERE t2.column_B2 = 'RW'
AND NOT EXISTS (
    SELECT 1 FROM tab2 WHERE column_A2 = t1.column_A1 AND column_B2 = 'PW'
);

逻辑说明：

• 第一部分查询：提取存在PW记录的分组，取PW对应的字段
• 第二部分查询：提取不存在PW记录的分组，取RW对应的字段
• 用UNION ALL合并两部分结果

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