如何通过主表ID关联多表并聚合关联字段值

我有三张表，结构如下：

表1（listing） #

表2（listingType） #

表3（listingCountry） #

我尝试通过表1的id关联表2和表3的fruitId来获取所有数据，执行了以下SQL：

SELECT * FROM listing 
    JOIN listingCountry ON (listing.id=listingCountry.fruitId) 
GROUP BY listing.id

（使用LEFT JOIN/UNION结果相同）

但仅得到单条关联数据，比如：apple : grany : europe，我需要的是聚合后的格式：apple: grany, adam, golden : Europe, Asia, America，请问如何编写SQL？

要实现将关联的type和country字段聚合为逗号分隔的字符串，需要使用对应数据库的字符串聚合函数，以下是主流数据库的实现方式：

MySQL/MariaDB #

使用GROUP_CONCAT()函数，默认用逗号分隔值，可通过DISTINCT去重（如有重复值）：

SELECT 
    l.fruit,
    GROUP_CONCAT(DISTINCT lt.type SEPARATOR ', ') AS types,
    GROUP_CONCAT(DISTINCT lc.country SEPARATOR ', ') AS countries
FROM listing l
LEFT JOIN listingType lt ON l.id = lt.fruitId
LEFT JOIN listingCountry lc ON l.id = lc.fruitId
GROUP BY l.id, l.fruit;

PostgreSQL #

使用STRING_AGG()函数，支持去重：

SELECT 
    l.fruit,
    STRING_AGG(DISTINCT lt.type, ', ') AS types,
    STRING_AGG(DISTINCT lc.country, ', ') AS countries
FROM listing l
LEFT JOIN listingType lt ON l.id = lt.fruitId
LEFT JOIN listingCountry lc ON l.id = lc.fruitId
GROUP BY l.id, l.fruit;

SQL Server #

2017及以上版本 #

直接使用STRING_AGG()：

SELECT 
    l.fruit,
    STRING_AGG(DISTINCT lt.type, ', ') AS types,
    STRING_AGG(DISTINCT lc.country, ', ') AS countries
FROM listing l
LEFT JOIN listingType lt ON l.id = lt.fruitId
LEFT JOIN listingCountry lc ON l.id = lc.fruitId
GROUP BY l.id, l.fruit;

2016及以下低版本 #

用STUFF+FOR XML PATH组合实现：

SELECT 
    l.fruit,
    STUFF((
        SELECT DISTINCT ', ' + lt.type
        FROM listingType lt
        WHERE lt.fruitId = l.id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS types,
    STUFF((
        SELECT DISTINCT ', ' + lc.country
        FROM listingCountry lc
        WHERE lc.fruitId = l.id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS countries
FROM listing l
GROUP BY l.id, l.fruit;

Oracle #

12cR2及以上版本 #

使用LISTAGG()结合DISTINCT：

SELECT 
    l.fruit,
    LISTAGG(DISTINCT lt.type, ', ') WITHIN GROUP (ORDER BY lt.type) AS types,
    LISTAGG(DISTINCT lc.country, ', ') WITHIN GROUP (ORDER BY lc.country) AS countries
FROM listing l
LEFT JOIN listingType lt ON l.id = lt.fruitId
LEFT JOIN listingCountry lc ON l.id = lc.fruitId
GROUP BY l.id, l.fruit;

低版本（不支持DISTINCT） #

先通过子查询去重再聚合：

SELECT 
    l.fruit,
    LISTAGG(lt.type, ', ') WITHIN GROUP (ORDER BY lt.type) AS types,
    LISTAGG(lc.country, ', ') WITHIN GROUP (ORDER BY lc.country) AS countries
FROM listing l
LEFT JOIN (SELECT DISTINCT fruitId, type FROM listingType) lt ON l.id = lt.fruitId
LEFT JOIN (SELECT DISTINCT fruitId, country FROM listingCountry) lc ON l.id = lc.fruitId
GROUP BY l.id, l.fruit;

补充说明 #

• 用LEFT JOIN替代JOIN，避免因某类无关联数据（如某水果无对应type或country）而被过滤。
• DISTINCT用于去除重复的type或country值，若数据无重复可省略。

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