DB2数据库角色与元素权限对比的SQL实现需求

数据库表结构 #

Element表 #

| elementId   int         |
| elementDesc VARCHAR(100)|

Role表 #

| roleId int            |
| roleName VARCHAR(100) |

ElementRole表 #

| elementId  int     |
| roleId int         |
| permission CHAR(1) |

数据示例 #

• Element表数据：[1, 'Save'], [2, 'Sale'], [3, 'Search']
• Role表数据：[1, 'Admin'], [2, 'User'], [3, 'View Only']
• ElementRole表数据：[1,1,'A'], [2,1,'A'], [3,1,'A'], [1, 2, 'A'], [3,2,'A']

说明： 角色1（Admin）拥有所有元素的权限，角色2（User）仅拥有3个元素中的2个权限，角色3（View Only）无任何权限记录。

需求 #

生成角色与元素的权限对比结果，格式如下：

Element, Admin, User, View Only
-------------------------------------
1    ,   A      ,  A   ,          
2    ,   A      ,      ,   
3    ,   A      ,  A   ,          

要求每个元素对应各角色的权限，无权限记录时显示为空，便于对比所有角色的权限。

问题 #

尝试过左连接、内连接等方式，但无法实现将角色转为列，尤其是无法处理无权限记录的情况。由于角色和元素数量较多，手动处理效率极低。使用DB2数据库，可先提供通用SQL方案，再自行转换为DB2语法。

解决方案 #

通用静态列SQL #

如果角色数量固定，使用条件聚合实现行转列，通过CROSS JOIN生成所有元素-角色组合，再左关联权限表填充数据：

SELECT
    e.elementId AS "Element",
    MAX(CASE WHEN r.roleName = 'Admin' THEN er.permission ELSE '' END) AS "Admin",
    MAX(CASE WHEN r.roleName = 'User' THEN er.permission ELSE '' END) AS "User",
    MAX(CASE WHEN r.roleName = 'View Only' THEN er.permission ELSE '' END) AS "View Only"
FROM
    Element e
CROSS JOIN
    Role r
LEFT JOIN
    ElementRole er ON e.elementId = er.elementId AND r.roleId = er.roleId
GROUP BY
    e.elementId
ORDER BY
    e.elementId;

DB2适配方案 #

静态列查询

和通用SQL完全一致，DB2支持该语法。

动态列查询（角色数量不固定）

如果角色数量会变化，可通过动态SQL自动生成列：

-- 生成列定义语句
WITH role_columns AS (
    SELECT LISTAGG('MAX(CASE WHEN roleName = ''' || roleName || ''' THEN permission ELSE '''' END) AS "' || roleName || '"', ', ')
    FROM Role
)
-- 输出完整查询语句
SELECT 'SELECT e.elementId AS "Element", ' || (SELECT * FROM role_columns) || ' 
FROM Element e
CROSS JOIN Role r
LEFT JOIN ElementRole er ON e.elementId = er.elementId AND r.roleId = er.roleId
GROUP BY e.elementId
ORDER BY e.elementId'
FROM SYSIBM.SYSDUMMY1;

执行上述SQL会得到完整的查询语句，再执行该语句即可生成包含所有角色列的权限对比结果。

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