如何在现有用户角色查询SQL中新增特定用户的已分配角色数量列

嘿，这个需求很容易实现——咱们可以用窗口函数来添加每个用户的已分配角色总数，不需要额外的子查询或者复杂的关联，代码也更简洁高效。

问题分析 #

你原来的SQL里，COUNT(*) cnt是基于GROUP BY pu.username, prdt.role_name...的分组计数，所以每个用户-角色的组合行只会返回1（因为每个分组都是唯一的用户+角色），这并不是你想要的“特定用户的总角色数量”。

修改后的SQL语句 #

SELECT 
    pu.username,
    prdt.role_name,
    prdt.description,
    prd.role_common_name,
    -- 新增列：计算当前用户的总角色数量
    COUNT(*) OVER (PARTITION BY pu.user_id) AS total_assigned_roles
FROM per_users pu
JOIN per_user_roles pur ON pur.user_id = pu.user_id
JOIN per_roles_dn prd ON prd.role_id = pur.role_id
JOIN per_roles_dn_tl prdt ON prdt.role_id = prd.role_id
GROUP BY pu.user_id, pu.username, prdt.role_name, prdt.description, prd.role_common_name

关键解释 #

• COUNT(*) OVER (PARTITION BY pu.user_id)：这是窗口函数的核心，它会以每个用户的user_id为分组（Partition），计算该分组内的总行数——也就是这个用户被分配的角色总数。用user_id而不是username更严谨，避免出现重名用户导致的统计错误。
• 保留原来的GROUP BY：如果你的数据中存在用户-角色的重复关联记录，GROUP BY可以确保每个用户-角色组合只显示一行，同时窗口函数依然能正确统计该用户的总角色数。如果数据本身没有重复，也可以去掉GROUP BY，结果一样。

示例效果 #

假设用户john_doe有3个角色，那么查询结果中john_doe的每一行都会在total_assigned_roles列显示3，同时每行展示他的一个具体角色信息。

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