用户与用户照片表关联查询问题：实现单用户多照片聚合

我需要编写SQL查询获取用户信息及其关联的用户照片，但当前执行的查询会返回重复的用户信息，仅照片URL不同。

当前使用的SQL语句 #

select * from User as a JOIN UserPhoto as up ON a.ID=up.UserId

用户表（User）结构及数据 #

FirstName | LastName |   DOB    | ID
ABC          YZV         123     1234567 
BCD          XYZ         123     1234568 
XYZ          MCD         124     1234569 
MNO          XYZ         125     1234570

用户照片表（UserPhoto）结构及数据 #

ID |   UserId   | PhotoUrl
01    1234567      https://imageUrl1
02    1234567      https://imageUrl2
03    1234568      https://imageUrla1
04    1234568      https://imageUrlb1

期望输出格式 #

{
  "fName": "ABC",
  "lName": "YZV", 
  "dob": 123, 
  "id": 1234567, 
  "photos": [
    "https://imageUrl1",
    "https://imageUrl2"
  ]
}

解决方案 #

原因分析 #

原始查询用JOIN关联用户表和照片表时，每个用户的每一张照片都会生成一条独立的记录，因此用户有多少张照片，就会返回多少条重复的用户信息。

1. 用聚合函数合并照片URL（适合返回字符串格式） #

根据不同数据库，使用对应的聚合函数将同一用户的照片URL合并为单个字符串：

• MySQL 使用GROUP_CONCAT：

SELECT 
  a.FirstName AS fName,
  a.LastName AS lName,
  a.DOB AS dob,
  a.ID AS id,
  GROUP_CONCAT(up.PhotoUrl SEPARATOR ',') AS photos
FROM User AS a
LEFT JOIN UserPhoto AS up ON a.ID = up.UserId
GROUP BY a.ID, a.FirstName, a.LastName, a.DOB;

• PostgreSQL/SQL Server 使用STRING_AGG：

SELECT 
  a.FirstName AS fName,
  a.LastName AS lName,
  a.DOB AS dob,
  a.ID AS id,
  STRING_AGG(up.PhotoUrl, ',') AS photos
FROM User AS a
LEFT JOIN UserPhoto AS up ON a.ID = up.UserId
GROUP BY a.ID, a.FirstName, a.LastName, a.DOB;

• Oracle 使用LISTAGG：

SELECT 
  a.FirstName AS fName,
  a.LastName AS lName,
  a.DOB AS dob,
  a.ID AS id,
  LISTAGG(up.PhotoUrl, ',') WITHIN GROUP (ORDER BY up.ID) AS photos
FROM User AS a
LEFT JOIN UserPhoto AS up ON a.ID = up.UserId
GROUP BY a.ID, a.FirstName, a.LastName, a.DOB;

2. 直接返回JSON格式（适合需要数组结构的场景） #

如果数据库支持JSON函数，可以直接生成符合期望格式的JSON：

• MySQL 8.0+ 使用JSON_OBJECT和JSON_ARRAYAGG：

SELECT 
  JSON_OBJECT(
    'fName', a.FirstName,
    'lName', a.LastName,
    'dob', a.DOB,
    'id', a.ID,
    'photos', JSON_ARRAYAGG(up.PhotoUrl)
  ) AS user_info
FROM User AS a
LEFT JOIN UserPhoto AS up ON a.ID = up.UserId
GROUP BY a.ID, a.FirstName, a.LastName, a.DOB;

• PostgreSQL 使用json_build_object和json_agg：

SELECT 
  json_build_object(
    'fName', a.FirstName,
    'lName', a.LastName,
    'dob', a.DOB,
    'id', a.ID,
    'photos', json_agg(up.PhotoUrl)
  ) AS user_info
FROM User AS a
LEFT JOIN UserPhoto AS up ON a.ID = up.UserId
GROUP BY a.ID, a.FirstName, a.LastName, a.DOB;

3. 应用层处理 #

如果数据库不支持复杂JSON操作，也可以先用原始JOIN查询获取所有数据，然后在应用代码中遍历结果，将同一用户的照片URL整理成数组，最终组装成期望的格式。

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