多对多关系下用户角色权限聚合SQL查询问题

核心思路 #

要实现用户权限的聚合（直接权限+角色继承权限），需分步骤预计算各关联数据，再合并去重：

1. 预计算每个角色对应的权限集合，避免重复关联查询
2. 计算每个用户的角色列表及各角色的权限集合
3. 单独提取用户的直接权限集合
4. 合并用户直接权限与角色继承权限，去重后得到最终权限数组

错误原因分析 #

你遇到的column "p_role_id" specified in USING clause does not exist in left table错误，是因为USING子句要求关联的两张表必须存在同名的关联列。比如你可能错误地将role_permission表的role_id写成了p_role_id，或者左表（如user_role）没有这个列。解决时优先用ON子句明确关联条件，避免依赖列名匹配。

PostgreSQL 实现示例 #

WITH role_perms AS (
    -- 预计算每个角色的权限数组
    SELECT 
        role_id,
        array_agg(DISTINCT permission_name) AS role_permissions
    FROM role_permission rp
    JOIN permission p ON rp.permission_id = p.permission_id
    GROUP BY role_id
),
user_roles_data AS (
    -- 计算用户的角色列表及对应角色的权限数组
    SELECT 
        ur.user_id,
        array_agg(DISTINCT r.role_name) AS user_role_array,
        array_agg(rp.role_permissions) AS roles_permissions_array
    FROM user_role ur
    JOIN role r ON ur.role_id = r.role_id
    JOIN role_perms rp ON ur.role_id = rp.role_id
    GROUP BY ur.user_id
),
user_direct_perms AS (
    -- 提取用户直接拥有的权限数组
    SELECT 
        up.user_id,
        array_agg(DISTINCT p.permission_name) AS direct_permissions
    FROM user_permission up
    JOIN permission p ON up.permission_id = p.permission_id
    GROUP BY up.user_id
)
SELECT 
    u.user_id,
    u.user_name,
    u.user_email,
    -- 处理无角色的用户，返回空数组
    COALESCE(urd.user_role_array, '{}'::text[]) AS user_role_array,
    -- 合并直接权限与角色权限并去重
    ARRAY(
        SELECT DISTINCT unnest(
            COALESCE(urd.role_permissions_flat, '{}'::text[]) || COALESCE(udp.direct_permissions, '{}'::text[])
        )
    ) AS user_permissions_array,
    COALESCE(urd.roles_permissions_array, '{}'::text[]) AS roles_permissions_array
FROM "user" u
LEFT JOIN (
    -- 将角色的多维权限数组展开为一维，方便合并
    SELECT 
        user_id,
        user_role_array,
        roles_permissions_array,
        array_agg(unnest(roles_permissions_array)) AS role_permissions_flat
    FROM user_roles_data
    GROUP BY user_id, user_role_array, roles_permissions_array
) urd ON u.user_id = urd.user_id
LEFT JOIN user_direct_perms udp ON u.user_id = udp.user_id
GROUP BY u.user_id, u.user_name, u.user_email, urd.user_role_array, urd.roles_permissions_array, urd.role_permissions_flat, udp.direct_permissions;

MySQL 实现示例 #

MySQL无原生数组类型，用字符串模拟集合：

WITH role_perms AS (
    SELECT 
        role_id,
        GROUP_CONCAT(DISTINCT permission_name SEPARATOR ',') AS role_permissions
    FROM role_permission rp
    JOIN permission p ON rp.permission_id = p.permission_id
    GROUP BY role_id
),
user_roles_data AS (
    SELECT 
        ur.user_id,
        GROUP_CONCAT(DISTINCT r.role_name SEPARATOR ',') AS user_role_array,
        GROUP_CONCAT(rp.role_permissions SEPARATOR ';') AS roles_permissions_array
    FROM user_role ur
    JOIN role r ON ur.role_id = r.role_id
    JOIN role_perms rp ON ur.role_id = rp.role_id
    GROUP BY ur.user_id
),
user_direct_perms AS (
    SELECT 
        up.user_id,
        GROUP_CONCAT(DISTINCT p.permission_name SEPARATOR ',') AS direct_permissions
    FROM user_permission up
    JOIN permission p ON up.permission_id = p.permission_id
    GROUP BY up.user_id
)
SELECT 
    u.user_id,
    u.user_name,
    u.user_email,
    COALESCE(urd.user_role_array, '') AS user_role_array,
    -- 合并去重：通过拆分字符串后去重再拼接
    (SELECT GROUP_CONCAT(DISTINCT val SEPARATOR ',') FROM (
        -- 拆分角色权限字符串
        SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(COALESCE(urd.roles_permissions_array, ''), ';'), ';', n.n), ';', -1)) AS val
        FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) n
        WHERE n.n <= LENGTH(CONCAT(COALESCE(urd.roles_permissions_array, ''), ';')) - LENGTH(REPLACE(CONCAT(COALESCE(urd.roles_permissions_array, ''), ';'), ';', ''))
        UNION
        -- 拆分直接权限字符串
        SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(COALESCE(udp.direct_permissions, ''), ',', n.n), ',', -1)) AS val
        FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) n
        WHERE n.n <= LENGTH(COALESCE(udp.direct_permissions, '')) - LENGTH(REPLACE(COALESCE(udp.direct_permissions, ''), ',', '')) + 1
    ) t WHERE val != '') AS user_permissions_array,
    COALESCE(urd.roles_permissions_array, '') AS roles_permissions_array
FROM user u
LEFT JOIN user_roles_data urd ON u.user_id = urd.user_id
LEFT JOIN user_direct_perms udp ON u.user_id = udp.user_id;

注意事项 #

• MySQL中拆分字符串时，n的取值范围需根据系统中最大权限数调整，也可用递归CTE生成连续数字
• 其他数据库（如SQL Server）可替换为对应聚合函数（如STRING_AGG）和数组处理函数

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