Oracle SQL内连接查询重复记录时返回指定角色ID方法

• 涉及库表：members 成员表、roles 角色表
• 关联逻辑：roles 表通过公共字段 MEMBER_NO 和成员绑定，存储每个成员关联的角色ID role_id，本次场景仅涉及1、2两类角色
• 示例绑定关系：
  • 成员1：同时关联角色1、角色2
  • 成员2：仅关联角色1
  • 成员3：仅关联角色2
• 原有查询语句：

SELECT m.member_id, r.role_id
FROM members m
INNER JOIN roles r ON m.MEMBER_NO =  r.MEMBER_NO

• 原有语句问题：关联多个角色的成员会返回多条重复记录，例如成员1会返回role_id为1、2的两行结果
• 目标规则：
  • 成员关联多个角色时，仅返回role_id=2的单条记录
  • 成员仅关联单个角色时，直接返回对应角色的记录
  • 结果示例：成员1最终仅返回role_id=2的单行结果

方案1：适配当前固定场景（仅1、2两类角色） #

因为角色2优先级高于角色1，直接用聚合函数取同成员下最大的role_id即可，写法简洁执行效率高：

SELECT m.member_id, MAX(r.role_id) AS role_id
FROM members m
INNER JOIN roles r ON m.MEMBER_NO = r.MEMBER_NO
GROUP BY m.member_id;

逻辑说明：同一个成员如果绑定了角色2，MAX(role_id)会返回2；如果仅绑定角色1，返回值为1，完全匹配当前需求。

方案2：通用可扩展方案 #

如果后续可能新增其他角色ID，需要明确指定角色2的最高优先级，可使用窗口函数实现，后续调整优先级只需要修改排序规则即可：

SELECT member_id, role_id
FROM (
    SELECT 
        m.member_id,
        r.role_id,
        ROW_NUMBER() OVER (
            PARTITION BY m.member_id
            ORDER BY CASE WHEN r.role_id = 2 THEN 0 ELSE 1 END
        ) AS sort_rn
    FROM members m
    INNER JOIN roles r ON m.MEMBER_NO = r.MEMBER_NO
) t
WHERE sort_rn = 1;

逻辑说明：通过ROW_NUMBER()给每个成员关联的角色排序，把角色2的排序优先级设为最高，最终只取每个成员排序后的第一条记录，不管后续新增多少种角色，都能保证优先返回角色2，没有角色2时再返回其他绑定角色。

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