创建SQL视图合并权限组以展示用户权限的技术咨询

我有三张表：

• users：包含id、name、email、password字段；
• permissiongroups：包含id、name，以及viewUsers、updateUsers等多个tinyint类型的权限字段；
• user_has_permissiongroups：包含id、userId、permissiongroupId、userIdCreator、active字段。

需要创建一个视图，合并用户所属的多个权限组的权限：只要用户所属的任意一个权限组拥有某权限，该权限就显示为1（true），否则为0（false）。

尝试过以下SQL，但无法正确合并权限：

SELECT * 
FROM user_has_permissiongroups
LEFT JOIN permissiongroups
ON user_has_permissiongroups.permissiongroupId = permissiongroups.id
WHERE user_has_permissiongroups.active = 1
GROUP BY userId

需要解决：

1. 编写正确的SELECT语句实现需求；
2. 确认是否可以在SQL中完成，无需业务代码处理；
3. 适配较多的权限字段，无需手动逐个指定。

示例数据与期望输出 #

期望输出 #

permissiongroups表示例数据 #

user_has_permissiongroups表示例数据 #

完全可以在SQL中实现权限合并，无需业务代码处理，核心思路是利用聚合函数MAX()：只要用户所属的任意一个权限组的该权限为1，MAX()就会返回1，否则返回0。

1. 手动指定权限字段（适用于权限字段固定的场景） #

如果权限字段数量不多且固定，可以直接写出所有权限字段的聚合逻辑：

CREATE VIEW user_combined_permissions AS
SELECT
    uhpg.userId,
    MAX(pg.viewUsers) AS viewUsers,
    MAX(pg.updateUsers) AS updateUsers,
    MAX(pg.createUsers) AS createUsers,
    MAX(pg.deleteUsers) AS deleteUsers
FROM user_has_permissiongroups uhpg
INNER JOIN permissiongroups pg 
    ON uhpg.permissiongroupId = pg.id
WHERE uhpg.active = 1
GROUP BY uhpg.userId;

执行该语句后，查询视图user_combined_permissions即可得到期望的合并权限结果。

2. 自动适配所有权限字段（适用于权限字段较多或会新增的场景） #

如果权限字段数量多或后续会新增，可以通过动态生成SQL自动获取所有权限字段，无需手动修改语句。以下以MySQL为例：

-- 初始化变量存储动态SQL
SET @sql = NULL;

-- 查询permissiongroups表中所有非基础字段（排除id、name），生成MAX聚合语句
SELECT GROUP_CONCAT(
    CONCAT('MAX(pg.', column_name, ') AS ', column_name)
) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = DATABASE()  -- 当前数据库
  AND table_name = 'permissiongroups'
  AND column_name NOT IN ('id', 'name');  -- 排除非权限字段

-- 拼接完整的创建视图语句
SET @sql = CONCAT(
    'CREATE VIEW user_combined_permissions AS SELECT uhpg.userId, ',
    @sql,
    ' FROM user_has_permissiongroups uhpg JOIN permissiongroups pg ON uhpg.permissiongroupId = pg.id WHERE uhpg.active = 1 GROUP BY uhpg.userId;'
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

说明 #

• 这段SQL会自动读取permissiongroups表中除id、name外的所有字段（即所有权限字段），生成对应的聚合逻辑；
• 如果后续新增权限字段，只需重新执行这段动态SQL，即可更新视图包含新的权限字段；
• 不同数据库的系统表语法略有差异：
  • PostgreSQL：将INFORMATION_SCHEMA.COLUMNS改为information_schema.columns，GROUP_CONCAT改为STRING_AGG；
  • SQL Server：使用sys.columns查询字段，用STRING_AGG拼接语句。

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