PostgreSQL多表查询：如何合并用户多角色为单行展示

当然可以实现！在PostgreSQL中，你可以借助STRING_AGG()聚合函数，配合GROUP BY子句来将同一个用户的多个角色拼接成单一字符串，从而让每个用户仅返回一行记录。

修改后的SQL查询语句 #

SELECT 
    ud.user_id, 
    ud.organisation, 
    CONCAT(COALESCE(ud.first_name,''),' ',COALESCE(ud.middle_name,''),' ',COALESCE(ud.last_name,'')) AS full_name, 
    STRING_AGG(ur.role, ',') AS role 
FROM user_details ud 
INNER JOIN user_roles ur ON ud.user_id = ur.user_name  -- 这里修正了字段名拼写：原语句里的`username`是笔误，对应表字段是`user_name`
GROUP BY ud.user_id, ud.organisation, full_name;

关键说明 #

• STRING_AGG(ur.role, ',')：这个函数会将分组内的role字段值用逗号分隔拼接成一个字符串，第一个参数是要聚合的字段，第二个是分隔符（你可以根据需求换成其他符号，比如';'）。
• GROUP BY ud.user_id, ud.organisation, full_name：必须对SELECT中所有非聚合字段进行分组，这样PostgreSQL才能明确哪些字段属于同一个用户的唯一标识，将对应角色聚合在一起。

执行后得到的结果 #

user_id | organisation | full_name         | role 
--------+--------------+-------------------+---------------------------------------
 user1   | organisation 1| first middle last | ROLE_REGISTEREDUSER,ROLE_ADMIN
 user 2  | org 2        | first middle last | ROLE_REGISTEREDUSER,ROLE_ADMIN

如果希望角色按特定顺序拼接，还可以在STRING_AGG里加上排序规则，比如：

STRING_AGG(ur.role, ',' ORDER BY ur.role) AS role

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