如何实现左表无重复记录的Left Outer JOIN查询

表结构与数据 #

表A（id, name） #

表B（id, productId, serviceId） #

表B中一个id可对应多组productId与serviceId（产品与服务为多对多关系），数据如下：

需求 #

获取表A中每个id及其关联的productId和serviceId，但表A的id不能重复出现（即每个主表ID仅返回一行记录）。

当前问题 #

使用普通左关联语句会返回同一id的多行记录：

SELECT A.ID, A.NAME, B.PRODUCTID, B.SERVICEID
FROM TABLE_A A
LEFT JOIN TABLE_B B
ON A.ID = B.ID 
WHERE some conditions ; 

解决方案 #

方案1：将多值拼接为字符串（最通用） #

通过聚合函数将同一ID下的所有产品-服务组合拼接成单个字符串，适合不需要结构化数据的场景。不同数据库的聚合函数略有差异：

MySQL 示例

SELECT 
    A.ID, 
    A.NAME, 
    -- 拼接产品与服务，去重后用分号分隔
    GROUP_CONCAT(DISTINCT CONCAT(B.PRODUCTID, ':', B.SERVICEID) SEPARATOR '; ') AS product_service
FROM TABLE_A A
LEFT JOIN TABLE_B B ON A.ID = B.ID 
WHERE some conditions
GROUP BY A.ID, A.NAME;

SQL Server 示例

SELECT 
    A.ID, 
    A.NAME, 
    STRING_AGG(DISTINCT CONCAT(B.PRODUCTID, ':', B.SERVICEID), '; ') AS product_service
FROM TABLE_A A
LEFT JOIN TABLE_B B ON A.ID = B.ID 
WHERE some conditions
GROUP BY A.ID, A.NAME;

Oracle 示例

SELECT 
    A.ID, 
    A.NAME, 
    LISTAGG(DISTINCT B.PRODUCTID || ':' || B.SERVICEID, '; ') WITHIN GROUP (ORDER BY B.PRODUCTID) AS product_service
FROM TABLE_A A
LEFT JOIN TABLE_B B ON A.ID = B.ID 
WHERE some conditions
GROUP BY A.ID, A.NAME;

方案2：聚合为JSON数组（结构化场景） #

如果需要保留产品与服务的结构化信息，可以将多组数据聚合为JSON数组，适合后续需要解析数据的场景：

MySQL 5.7+ 示例

SELECT 
    A.ID, 
    A.NAME, 
    JSON_ARRAYAGG(DISTINCT JSON_OBJECT('productId', B.PRODUCTID, 'serviceId', B.SERVICEID)) AS product_service_list
FROM TABLE_A A
LEFT JOIN TABLE_B B ON A.ID = B.ID 
WHERE some conditions
GROUP BY A.ID, A.NAME;

PostgreSQL 示例

SELECT 
    A.ID, 
    A.NAME, 
    json_agg(DISTINCT json_build_object('productId', B.PRODUCTID, 'serviceId', B.SERVICEID)) AS product_service_list
FROM TABLE_A A
LEFT JOIN TABLE_B B ON A.ID = B.ID 
WHERE some conditions
GROUP BY A.ID, A.NAME;

方案3：仅取单条关联记录（不需要所有多值） #

如果只需要每个主表ID对应任意一条或特定规则的关联记录，可以用窗口函数筛选：

SELECT 
    A.ID, 
    A.NAME, 
    B.PRODUCTID, 
    B.SERVICEID
FROM TABLE_A A
LEFT JOIN (
    SELECT 
        ID, 
        PRODUCTID, 
        SERVICEID,
        -- 按ID分组，给每条记录编号，取第一条
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY PRODUCTID, SERVICEID) AS rn
    FROM TABLE_B
    WHERE some conditions
) B ON A.ID = B.ID AND B.rn = 1;

调整ORDER BY子句可以指定取哪条记录（比如最新创建的、按产品名称排序的第一条）。

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