如何修改内连接查询以返回单用户唯一行并列出其所有组与角色

嘿，这个问题我碰到过好多次了——当你关联多对多的角色和组关系时，很容易因为一个用户对应多个角色而拉出重复的用户行。要让每个用户只返回一行，同时把所有关联的组和角色都列出来，你需要用字符串聚合函数，不同数据库的函数不一样，我给你分主流数据库整理了方案：

先明确下你的表关系：

• user：存储用户基础信息
• user_group：用户与组的关联表（仅user_fk、group_fk两列）
• group：组信息表
• group_role：组与角色的关联表（仅group_fk、role_fk两列）
• role：角色信息表

你的原查询因为一个组对应多个角色，所以会返回多行相同用户的记录，下面是修改后的查询：

MySQL (5.7+) / MariaDB #

使用GROUP_CONCAT()函数聚合字符串，记得按用户唯一字段分组：

SELECT 
  u.last_name, 
  u.first_name, 
  u.user_name,
  -- 聚合用户所属的所有组名，去重并用逗号分隔
  GROUP_CONCAT(DISTINCT g.name SEPARATOR ', ') AS group_names,
  -- 聚合用户关联的所有角色名
  GROUP_CONCAT(DISTINCT r.name SEPARATOR ', ') AS role_names
FROM user u
INNER JOIN user_group ug ON ug.user_fk = u.id
INNER JOIN `group` g ON g.id = ug.group_fk
INNER JOIN group_role gr ON g.id = gr.group_fk
INNER JOIN role r ON gr.role_fk = r.id
GROUP BY u.id, u.last_name, u.first_name, u.user_name;

注意：group是SQL关键字，必须用反引号`包裹，否则会触发语法错误，你的原查询其实也需要这个处理哦。

PostgreSQL #

使用STRING_AGG()函数实现聚合：

SELECT 
  u.last_name, 
  u.first_name, 
  u.user_name,
  STRING_AGG(DISTINCT g.name, ', ') AS group_names,
  STRING_AGG(DISTINCT r.name, ', ') AS role_names
FROM "user" u
INNER JOIN user_group ug ON ug.user_fk = u.id
INNER JOIN "group" g ON g.id = ug.group_fk
INNER JOIN group_role gr ON g.id = gr.group_fk
INNER JOIN role r ON gr.role_fk = r.id
GROUP BY u.id, u.last_name, u.first_name, u.user_name;

PostgreSQL中关键字需要用双引号"包裹。

SQL Server (2017+) #

同样用STRING_AGG()函数，语法略有不同：

SELECT 
  u.last_name, 
  u.first_name, 
  u.user_name,
  STRING_AGG(DISTINCT g.name, ', ') AS group_names,
  STRING_AGG(DISTINCT r.name, ', ') AS role_names
FROM [user] u
INNER JOIN user_group ug ON ug.user_fk = u.id
INNER JOIN [group] g ON g.id = ug.group_fk
INNER JOIN group_role gr ON g.id = gr.group_fk
INNER JOIN role r ON gr.role_fk = r.id
GROUP BY u.id, u.last_name, u.first_name, u.user_name;

SQL Server里关键字用方括号[]包裹即可。

Oracle (11g+) #

使用LISTAGG()函数，需要指定排序规则：

SELECT 
  u.last_name, 
  u.first_name, 
  u.user_name,
  LISTAGG(DISTINCT g.name, ', ') WITHIN GROUP (ORDER BY g.name) AS group_names,
  LISTAGG(DISTINCT r.name, ', ') WITHIN GROUP (ORDER BY r.name) AS role_names
FROM "USER" u
INNER JOIN user_group ug ON ug.user_fk = u.id
INNER JOIN "GROUP" g ON g.id = ug.group_fk
INNER JOIN group_role gr ON g.id = gr.group_fk
INNER JOIN role r ON gr.role_fk = r.id
GROUP BY u.id, u.last_name, u.first_name, u.user_name;

Oracle中关键字需要用双引号，LISTAGG必须搭配WITHIN GROUP (ORDER BY ...)使用。

额外小提示 #

• 加DISTINCT是为了避免同一个组/角色因为关联关系被重复列出（比如一个用户通过多个组关联到同一个角色时，会自动去重）。
• 你可以修改SEPARATOR里的分隔符，比如改成'; '或者' | '，适配你的展示需求。
• 如果用户属于多个组，上面的查询也会把所有组名合并到一行里，完美符合你的需求。

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