如何用原生SQL查询user表按role分组聚合同角色下关联的所有name

核心实现逻辑 #

你需要使用GROUP BY按role字段分组，搭配对应数据库的聚合函数实现同角色下所有name的合并，仅查询admin角色的写法按数据库区分如下：

• MySQL
  5.7及以上版本使用GROUP_CONCAT返回拼接字符串，8.0以上可使用JSON_ARRAYAGG返回数组格式：

  -- 返回逗号分隔的用户名拼接结果
  SELECT role, GROUP_CONCAT(name SEPARATOR ', ') AS all_names
  FROM `user`
  WHERE role = 'admin'
  GROUP BY role;
  
  -- 返回JSON数组格式的用户名集合
  SELECT role, JSON_ARRAYAGG(name) AS name_array
  FROM `user`
  WHERE role = 'admin'
  GROUP BY role;
  

• PostgreSQL
  可使用STRING_AGG返回拼接字符串，或ARRAY_AGG返回数组：

  -- 字符串格式
  SELECT role, STRING_AGG(name, ', ') AS all_names
  FROM "user"
  WHERE role = 'admin'
  GROUP BY role;
  
  -- 数组格式
  SELECT role, ARRAY_AGG(name) AS name_array
  FROM "user"
  WHERE role = 'admin'
  GROUP BY role;
  

• SQL Server
  使用STRING_AGG实现：

  SELECT role, STRING_AGG(name, ', ') AS all_names
  FROM [user]
  WHERE role = 'admin'
  GROUP BY role;
  

• Oracle
  11g及以上版本使用LISTAGG实现：

  SELECT role, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS all_names
  FROM "USER"
  WHERE role = 'admin'
  GROUP BY role;
  

拓展说明 #

如果需要查询所有角色对应的聚合结果，直接删除WHERE role = 'admin'过滤条件即可。

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