SQL如何连接4张表查询用户可访问的所有页面（含直接和用户组权限）

问题原因分析 #

• 字段名错误：pages表主键字段为page_id而非id，关联条件字段对应错误
• 表名拼写错误：代码中引用的user_group_实际表名为group_user，关联表名匹配错误
• 关联逻辑错误：使用INNER JOIN同时关联user_page和group_page，相当于要求页面同时满足「直接授予用户」和「授予用户组」两个条件，漏掉了只满足单一授权条件的页面
• 过滤逻辑不全：WHERE条件仅过滤了直接授权的用户ID，没有覆盖用户组关联的用户权限场景

查询指定用户所有可访问页面的正确SQL #

推荐使用UNION写法，逻辑清晰性能更稳定：

-- 直接授予用户的页面
SELECT DISTINCT
    p.page_id,
    p.page_name,
    p.page_description
FROM pages p
JOIN user_page up ON p.page_id = up.page_id
WHERE up.user_id = 23

UNION

-- 用户通过所在组间接获得权限的页面
SELECT DISTINCT
    p.page_id,
    p.page_name,
    p.page_description
FROM pages p
JOIN group_page gp ON p.page_id = gp.page_id
JOIN group_user gu ON gp.group_id = gu.group_id
WHERE gu.user_id = 23

代入示例数据中的用户ID=23执行，返回结果为page_id=4（直接授权）和page_id=6（用户23属于组16，组16有page6的访问权限），符合预期。

查询指定用户是否有指定页面访问权限的SQL #

以查询用户ID=7是否可访问page_id=4的需求为例：

SELECT COUNT(*) AS has_permission
FROM (
    -- 匹配直接授权
    SELECT page_id FROM user_page WHERE user_id = 7 AND page_id = 4
    UNION
    -- 匹配组间接授权
    SELECT gp.page_id 
    FROM group_page gp
    JOIN group_user gu ON gp.group_id = gu.group_id
    WHERE gu.user_id = 7 AND gp.page_id = 4
) t

返回结果大于0代表有权限，等于0代表无权限。根据你提供的示例数据，用户7既没有page4的直接授权，也不在任何拥有page4访问权限的组内，查询结果为0，无访问权限。

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