PostgreSQL如何仅当table_2匹配行唯一时执行inner join

你可以先对table_2按关联字段（name、age）统计匹配次数，过滤出仅出现1次的分组后再和table_1做关联即可，有两种常见实现方式：

方案1：窗口函数预处理（可直接在JOIN条件中加过滤逻辑） #

-- 先给table_2的每个name+age分组统计匹配条数
WITH table2_with_cnt AS (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY name, age) AS match_num
    FROM table_2
)
SELECT 
    tb.id,
    tb.name,
    tb.age,
    sc.hair
FROM table_1 tb
INNER JOIN table2_with_cnt sc
    ON tb.name = sc.name 
    AND tb.age = sc.age
    AND sc.match_num = 1; -- 仅保留唯一匹配的行

方案2：子查询筛选唯一匹配键 #

SELECT 
    tb.id,
    tb.name,
    tb.age,
    sc.hair
FROM table_1 tb
INNER JOIN table_2 sc
    ON tb.name = sc.name 
    AND tb.age = sc.age
-- 关联筛选出table_2中仅出现1次的name+age组合
INNER JOIN (
    SELECT name, age
    FROM table_2
    GROUP BY name, age
    HAVING COUNT(*) = 1
) sc_unique
    ON sc.name = sc_unique.name 
    AND sc.age = sc_unique.age;

两种写法最终输出结果和你预期的一致，会自动排除table_2中存在多条匹配的john jones，以及table_2中无匹配的amy roberts。

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