关联users与pic_urls表时，如何将用户多图片URL聚合为数组？

要实现每个用户对应多张图片URL的数组聚合，核心是使用分组聚合函数，不同数据库的语法略有差异，以下是主流数据库的实现方式：

PostgreSQL #

使用array_agg()函数直接将URL聚合为数组：

SELECT
  users.id,
  users.firstname,
  users.lastname,
  array_agg(pic_urls.url) AS pic_urls
FROM users
JOIN pic_urls ON users.id = pic_urls.user_id
WHERE users.id != ?
GROUP BY users.id, users.firstname, users.lastname;

MySQL #

版本8.0+（推荐） #

使用JSON_ARRAYAGG()生成标准JSON数组：

SELECT
  users.id,
  users.firstname,
  users.lastname,
  JSON_ARRAYAGG(pic_urls.url) AS pic_urls
FROM users
JOIN pic_urls ON users.id = pic_urls.user_id
WHERE users.id != ?
GROUP BY users.id, users.firstname, users.lastname;

版本5.7及以下 #

用GROUP_CONCAT()拼接字符串，手动包裹数组格式（如需JSON格式）：

SELECT
  users.id,
  users.firstname,
  users.lastname,
  CONCAT('[', GROUP_CONCAT('"', pic_urls.url, '"'), ']') AS pic_urls
FROM users
JOIN pic_urls ON users.id = pic_urls.user_id
WHERE users.id != ?
GROUP BY users.id, users.firstname, users.lastname;

注意：GROUP_CONCAT有默认长度限制，可通过group_concat_max_len参数调整。

SQL Server #

版本2017+ #

使用STRING_AGG()拼接字符串，或结合JSON_QUERY生成JSON数组：

-- 生成逗号分隔的字符串
SELECT
  users.id,
  users.firstname,
  users.lastname,
  STRING_AGG(pic_urls.url, ',') AS pic_urls
FROM users
JOIN pic_urls ON users.id = pic_urls.user_id
WHERE users.id != ?
GROUP BY users.id, users.firstname, users.lastname;

-- 生成JSON数组
SELECT
  users.id,
  users.firstname,
  users.lastname,
  JSON_QUERY('["' + STRING_AGG(REPLACE(pic_urls.url, '"', '""'), '","') + '"]') AS pic_urls
FROM users
JOIN pic_urls ON users.id = pic_urls.user_id
WHERE users.id != ?
GROUP BY users.id, users.firstname, users.lastname;

SQLite #

版本3.33.0+ #

使用JSON_GROUP_ARRAY()生成JSON数组：

SELECT
  users.id,
  users.firstname,
  users.lastname,
  JSON_GROUP_ARRAY(pic_urls.url) AS pic_urls
FROM users
JOIN pic_urls ON users.id = pic_urls.user_id
WHERE users.id != ?
GROUP BY users.id, users.firstname, users.lastname;

旧版本 #

用GROUP_CONCAT()拼接字符串：

SELECT
  users.id,
  users.firstname,
  users.lastname,
  GROUP_CONCAT(pic_urls.url) AS pic_urls
FROM users
JOIN pic_urls ON users.id = pic_urls.user_id
WHERE users.id != ?
GROUP BY users.id, users.firstname, users.lastname;

关键注意事项 #

• 必须按users表的所有非聚合字段（id、firstname、lastname）分组，否则会出现分组错误。
• 如果用户可能没有关联的图片URL，需将JOIN改为LEFT JOIN，此时聚合结果会返回NULL或空数组（依数据库函数而定）。

内容的提问来源于stack exchange，提问作者Bobby Wan-Kenobi