如何通过SQL对比两张表获取用户缺失的权限功能？

先假设核心表结构（可根据实际表名/字段调整）：

• role_functions：角色功能配置表，字段：role（角色名称）、function（功能名称）
• user_roles：用户角色关联表，字段：user_name（用户名）、role（所属角色）
• user_functions：用户功能分配表，字段：user_name（用户名）、function（已分配功能）

1. 查询包含用户已有功能及缺失功能的结果 #

先生成所有用户理应拥有的功能集合，再与用户实际分配的功能做左连接，缺失的功能会对应user_name为null：

-- 生成用户应有的全量功能
WITH user_required_functions AS (
    SELECT ur.user_name, rf.function
    FROM user_roles ur
    JOIN role_functions rf ON ur.role = rf.role
)
-- 左连接实际分配表，输出已有/缺失的完整列表
SELECT 
    uf.user_name,
    urf.function,
    CASE WHEN uf.user_name IS NOT NULL THEN '已拥有' ELSE '缺失' END AS status
FROM user_required_functions urf
LEFT JOIN user_functions uf 
    ON urf.user_name = uf.user_name 
    AND urf.function = uf.function
ORDER BY urf.user_name, urf.function;

如果不需要状态标记，仅保留用户和功能字段，可简化为：

WITH user_required_functions AS (
    SELECT ur.user_name, rf.function
    FROM user_roles ur
    JOIN role_functions rf ON ur.role = rf.role
)
SELECT 
    uf.user_name,
    urf.function
FROM user_required_functions urf
LEFT JOIN user_functions uf 
    ON urf.user_name = uf.user_name 
    AND urf.function = uf.function
ORDER BY urf.user_name, urf.function;

2. 直接获取具体用户的缺失功能列表 #

筛选左连接后uf.user_name为null的记录，即为该用户缺失的功能：

WITH user_required_functions AS (
    SELECT ur.user_name, rf.function
    FROM user_roles ur
    JOIN role_functions rf ON ur.role = rf.role
)
SELECT 
    urf.user_name,
    urf.function AS missing_function
FROM user_required_functions urf
LEFT JOIN user_functions uf 
    ON urf.user_name = uf.user_name 
    AND urf.function = uf.function
WHERE uf.user_name IS NULL
ORDER BY urf.user_name, urf.function;

特殊场景适配 #

如果没有单独的user_roles表，user_functions中直接存储了用户-角色关联（字段包含user_name、role、function），可调整如下：

WITH user_required_functions AS (
    SELECT DISTINCT uf.user_name, rf.function
    FROM user_functions uf
    JOIN role_functions rf ON uf.role = rf.role
)
SELECT 
    urf.user_name,
    urf.function AS missing_function
FROM user_required_functions urf
LEFT JOIN user_functions uf 
    ON urf.user_name = uf.user_name 
    AND urf.function = uf.function
WHERE uf.user_name IS NULL
ORDER BY urf.user_name, urf.function;

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