MySQL多表连接时如何限制每个表单仅返回1条优先级最高的关联用户数据

MySQL 8.0+ 最优方案（无N+1、支持多表扩展） #

WITH RankedFormUsers AS (
    SELECT 
        uf.form_id,
        uf.user_id,
        uf.additional_comment,
        u.name,
        -- 优先级规则：admin > employee，同角色按用户ID升序取第一条
        ROW_NUMBER() OVER (
            PARTITION BY uf.form_id 
            ORDER BY CASE u.role WHEN 'admin' THEN 1 WHEN 'employee' THEN 2 ELSE 3 END, u.id ASC
        ) AS priority_rank
    FROM UsersAssignToForms uf
    INNER JOIN Users u ON uf.user_id = u.id
)
SELECT 
    rfu.user_id,
    f.id AS form_id,
    rfu.name AS first_name,
    f.type AS form_type,
    rfu.additional_comment
    -- 后续新增其他表的字段直接在这里加即可
FROM Forms f
LEFT JOIN RankedFormUsers rfu 
    ON f.id = rfu.form_id 
    AND rfu.priority_rank = 1
-- 后续新增其他关联表直接在这里追加JOIN语句即可，无需修改前面的优先级逻辑
-- 示例：INNER JOIN OtherTable ot ON f.id = ot.form_id

方案说明 #

• 每个表单最多返回1条用户数据：通过PARTITION BY uf.form_id按表单分组，仅取分组内排名第一的记录实现
• 优先级符合要求：排序规则中admin角色权重值更小，优先排在前面，同角色按用户ID升序取最早创建的用户
• 无关联用户的表单自动返回null：主表Forms左连接关联计算结果，无匹配记录时用户相关字段自动为null
• 扩展性极强：以Forms为核心基表，后续新增任意关联表都可以直接在主查询段追加JOIN语句，完全不需要调整优先级计算逻辑，整个查询为单次执行，不存在N+1性能问题

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