如何将SQL查询结果按City分组并合并Category字段值？

根据需求，需将关联查询结果按City分组，把同一城市的多个Category值合并为逗号分隔的字符串，以下是主流数据库的实现方案：

MySQL/MariaDB #

使用GROUP_CONCAT()函数实现字符串聚合：

SELECT 
    B.City,
    GROUP_CONCAT(C.Category SEPARATOR ', ') AS category
FROM Business B
INNER JOIN Category C ON B.id = C.business_id
GROUP BY B.City;

GROUP_CONCAT()默认以逗号分隔，显式指定SEPARATOR ', '可确保格式与需求完全匹配。

PostgreSQL #

使用STRING_AGG()函数：

SELECT 
    B.City,
    STRING_AGG(C.Category, ', ') AS category
FROM Business B
INNER JOIN Category C ON B.id = C.business_id
GROUP BY B.City;

SQL Server #

• SQL Server 2017及以上版本：直接使用STRING_AGG()

SELECT 
    B.City,
    STRING_AGG(C.Category, ', ') AS category
FROM Business B
INNER JOIN Category C ON B.id = C.business_id
GROUP BY B.City;

• SQL Server 2016及更早版本：通过STUFF结合FOR XML PATH实现

SELECT 
    DISTINCT B.City,
    STUFF((
        SELECT ', ' + C2.Category
        FROM Category C2
        WHERE C2.business_id = B.id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS category
FROM Business B
INNER JOIN Category C ON B.id = C.business_id;

Oracle #

使用LISTAGG()函数，可按需指定分类排序顺序：

SELECT 
    B.City,
    LISTAGG(C.Category, ', ') WITHIN GROUP (ORDER BY C.Category) AS category
FROM Business B
INNER JOIN Category C ON B.id = C.business_id
GROUP BY B.City;

若无需排序，可去掉ORDER BY C.Category部分。

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