SQL实现按tinyint标识字段分栏展示用户主角色与副角色

需求说明 #

需要从用户、角色关联表中提取用户信息，规则如下：

• 系统支持单个用户绑定多个主角色、多个副角色
• roles表中primary字段值为1的是主角色，聚合后返回至Role字段
• primary字段值为0的是副角色，聚合后返回至Subrole字段
• 最终结果集不需要返回primary标识字段，同一个用户仅返回单行结果，同时展示用户名、主角色列表、副角色列表、直属经理信息。

原有SQL问题 #

原有写法按roles.primary字段分组，会将同一个用户的主、副角色拆分为两行返回，不符合单行输出的要求。

优化后SQL #

核心逻辑是使用条件聚合，在聚合函数内判断角色类型分别聚合，分组维度调整为用户维度，代码如下：

SELECT
  CONCAT(users.first_name, ' ', users.last_name) AS 'User Name',
  -- 仅聚合主角色
  GROUP_CONCAT(DISTINCT CASE WHEN roles.primary = 1 THEN roles.role_name END SEPARATOR ',') AS 'Role',
  -- 仅聚合副角色
  GROUP_CONCAT(DISTINCT CASE WHEN roles.primary = 0 THEN roles.role_name END SEPARATOR ',') AS 'Subrole',
  CONCAT(managers.first_name, ' ', managers.last_name) AS 'Manager'
FROM users
  LEFT OUTER JOIN user_roles
    ON users.id = user_roles.user_id
  LEFT OUTER JOIN user_managers
    ON user_managers.user_id = users.id
  LEFT OUTER JOIN roles
    ON user_roles.role_id = roles.id
  LEFT OUTER JOIN users managers
    ON user_managers.manager_id = managers.id
WHERE users.first_name LIKE 'Dave%'
-- 按用户维度分组，保证单用户单行
GROUP BY users.id, users.first_name, users.last_name, managers.id, managers.first_name, managers.last_name;

关键说明 #

• 去掉了原SQL中多余的DISTINCT，分组逻辑正确时不需要额外全局去重
• GROUP_CONCAT内加DISTINCT是为了避免多表关联产生的重复角色值，保证角色列表不出现重复项
• 分组字段包含用户、经理的唯一标识和展示字段，符合SQL_MODE严格模式的语法要求，不会报错
• 如果业务中存在一个用户对应多个经理的场景，可以给经理字段也加上GROUP_CONCAT聚合，避免多行拆分。

执行后即可得到预期的单行结果：

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