SQL如何合并重复客户行并聚合对应宠物名称

解决方案 #

要实现将同一客户的宠物名称合并为列表的需求，不能依赖SELECT DISTINCT（它仅对整行去重，宠物名不同时整行仍会被判定为不同），核心思路是按客户分组，再用聚合函数合并宠物名称。以下是不同主流数据库的具体实现：

1. MySQL（5.7及以上版本） #

使用GROUP_CONCAT拼接宠物名，若要模拟数组格式，可通过字符串拼接包裹结果：

SELECT
  CUSTOMERS.ID,
  CUSTOMERS.NAME,
  CUSTOMERS.EMAIL,
  CONCAT('[', GROUP_CONCAT(DISTINCT PETS.NAME SEPARATOR ', '), ']') AS PET_NAMES
FROM CUSTOMERS
INNER JOIN PETS ON PETS.CUSTOMER_ID = CUSTOMERS.ID
GROUP BY CUSTOMERS.ID, CUSTOMERS.NAME, CUSTOMERS.EMAIL;

• 加DISTINCT可避免同一客户有重名宠物时重复显示
• GROUP BY必须包含所有非聚合字段（客户ID、姓名、邮箱）

2. PostgreSQL #

PostgreSQL原生支持数组类型，用ARRAY_AGG可直接生成符合需求的数组格式：

SELECT
  CUSTOMERS.ID,
  CUSTOMERS.NAME,
  CUSTOMERS.EMAIL,
  ARRAY_AGG(DISTINCT PETS.NAME) AS PET_NAMES
FROM CUSTOMERS
INNER JOIN PETS ON PETS.CUSTOMER_ID = CUSTOMERS.ID
GROUP BY CUSTOMERS.ID, CUSTOMERS.NAME, CUSTOMERS.EMAIL;

3. SQL Server（2017及以上版本） #

用STRING_AGG拼接宠物名，同样可模拟数组格式：

SELECT
  CUSTOMERS.ID,
  CUSTOMERS.NAME,
  CUSTOMERS.EMAIL,
  CONCAT('[', STRING_AGG(DISTINCT PETS.NAME, ', '), ']') AS PET_NAMES
FROM CUSTOMERS
INNER JOIN PETS ON PETS.CUSTOMER_ID = CUSTOMERS.ID
GROUP BY CUSTOMERS.ID, CUSTOMERS.NAME, CUSTOMERS.EMAIL;

如果是2017之前的旧版本，可使用STUFF+FOR XML PATH的兼容写法：

SELECT
  C.ID,
  C.NAME,
  C.EMAIL,
  CONCAT('[', STUFF(
    (SELECT DISTINCT ', ' + P.NAME
     FROM PETS P
     WHERE P.CUSTOMER_ID = C.ID
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''), ']') AS PET_NAMES
FROM CUSTOMERS C
INNER JOIN PETS P ON P.CUSTOMER_ID = C.ID
GROUP BY C.ID, C.NAME, C.EMAIL;

为什么SELECT DISTINCT无效？ #

DISTINCT是对整行数据进行去重判断，只要任意一列值不同（比如这里的宠物名称），整行就会被视为不同记录。因此即使添加DISTINCT，有多只宠物的客户仍会返回多行，必须通过GROUP BY按客户分组，再用聚合函数合并组内的宠物名称。

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