SQL查询拥有app1访问权限用户中各角色人数（按位运算场景）

实现方案 #

方案1：硬编码角色位（适合角色位固定不变的场景） #

直接在SQL中写入三个角色对应的按位判断值，写法简单高效：

SELECT
    SUM(CASE WHEN `app1` & 1 THEN 1 ELSE 0 END) AS `supplier`,
    SUM(CASE WHEN `app1` & 2 THEN 1 ELSE 0 END) AS `client`,
    SUM(CASE WHEN `app1` & 4 THEN 1 ELSE 0 END) AS `consultant`
FROM `authorized_users`
WHERE `permissions` & (
    SELECT `bits` FROM `user_permissions` 
    WHERE `permission_name` = 'app1' AND `app` = 'global'
);

注意：请根据你实际的角色位定义替换1/2/4三个数值，以上仅为示例，对应最低三位分别是supplier、client、consultant权限位的场景。

方案2：动态读取角色位（适合角色位可配置的场景） #

直接从user_permissions表读取三个角色对应的位值，不需要硬编码，可维护性更高：

SELECT
    SUM(CASE WHEN up.permission_name = 'supplier' AND au.app1 & up.bits THEN 1 ELSE 0 END) AS supplier,
    SUM(CASE WHEN up.permission_name = 'client' AND au.app1 & up.bits THEN 1 ELSE 0 END) AS client,
    SUM(CASE WHEN up.permission_name = 'consultant' AND au.app1 & up.bits THEN 1 ELSE 0 END) AS consultant
FROM authorized_users au
CROSS JOIN user_permissions up_global
LEFT JOIN user_permissions up 
    ON up.app = 'app1' 
    AND up.permission_name IN ('supplier', 'client', 'consultant')
WHERE 
    up_global.permission_name = 'app1' 
    AND up_global.app = 'global'
    AND au.permissions & up_global.bits;

说明 #

两种方案均满足以下要求：

• 统计范围仅包含拥有app1访问权限的用户，权限判断逻辑和提供的原有逻辑完全一致
• 单个用户同时拥有多个角色时，会被统计到所有对应角色的计数中
• 最终结果直接返回supplier、client、consultant三列，每列值为对应角色的用户总数

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