一对多关联SQL查询：优先取关联表指定值行且保证主表主键唯一

需求说明 #

实现一对多关联表的去重匹配查询，每个tableA的主键仅返回1条匹配的tableB记录，匹配优先级为columnA=2 > columnA=1。

实现方法 #

方法1：窗口函数法（通用，支持所有主流现代数据库，MySQL 8.0+、PostgreSQL、SQL Server、Oracle均可使用） #

该方法扩展性最强，后续如果调整优先级规则也可以快速修改：

SELECT a.id, b.columnA
FROM tableA a
LEFT JOIN (
    SELECT 
        fkid, 
        columnA,
        -- 按优先级排序，columnA=2的排第1，columnA=1的排第2
        ROW_NUMBER() OVER (PARTITION BY fkid ORDER BY CASE WHEN columnA = 2 THEN 1 ELSE 2 END ASC) AS rn
    FROM tableB
) b ON a.id = b.fkid AND b.rn = 1;

方法2：聚合函数法（更简洁，适配当前优先级规则，支持所有数据库包括低版本MySQL） #

由于当前优先级刚好是数值更大的columnA优先，直接取每个外键对应的最大columnA即可：

SELECT a.id, MAX(b.columnA) AS columnA
FROM tableA a
LEFT JOIN tableB b ON a.id = b.fkid
GROUP BY a.id;

测试结果 #

针对提供的测试数据，两种方法返回的结果一致：

扩展说明 #

如果后续优先级规则调整，比如优先取columnA=1再取columnA=2，只需要修改窗口函数的ORDER BY逻辑即可，不需要改动整体结构。

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