如何用SQL查询合并tableA与tableB生成含多行订单的单条记录

你的问题出在标量子查询返回了多行数据——SELECT子句中的子查询要求必须返回单个值，但tableB中customer_id=2对应3条订单记录，导致执行报错。以下是两种符合需求的解决方案：

方案1：将所有订单号合并为单个字符串 #

适合需要把订单号放在同一字段的场景，不同数据库使用对应的聚合函数：

MySQL/MariaDB #

使用GROUP_CONCAT聚合订单号：

SELECT 
    a.customer_id,
    a.name,
    GROUP_CONCAT(b.number SEPARATOR ', ') AS orders
FROM tableA a
JOIN tableB b ON a.customer_id = b.customer_id
WHERE a.customer_id = 2
GROUP BY a.customer_id, a.name;

返回结果：

PostgreSQL #

使用STRING_AGG函数（需将数字转为文本）：

SELECT 
    a.customer_id,
    a.name,
    STRING_AGG(b.number::TEXT, ', ') AS orders
FROM tableA a
JOIN tableB b ON a.customer_id = b.customer_id
WHERE a.customer_id = 2
GROUP BY a.customer_id, a.name;

SQL Server #

• 2017及以上版本用STRING_AGG：

SELECT 
    a.customer_id,
    a.name,
    STRING_AGG(b.number, ', ') AS orders
FROM tableA a
JOIN tableB b ON a.customer_id = b.customer_id
WHERE a.customer_id = 2
GROUP BY a.customer_id, a.name;

• 旧版本用STUFF+FOR XML PATH组合：

SELECT 
    a.customer_id,
    a.name,
    STUFF((
        SELECT ', ' + CAST(b.number AS VARCHAR)
        FROM tableB b
        WHERE b.customer_id = a.customer_id
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS orders
FROM tableA a
WHERE a.customer_id = 2;

方案2：实现视觉化分行显示（仅首行显示客户信息） #

如果要完全匹配你期望的tableC格式（首行显示客户信息，后续行仅显示订单号），可以用窗口函数标记首行，非首行的客户字段返回空值：

SELECT 
    CASE WHEN rn = 1 THEN customer_id END AS customer_id,
    CASE WHEN rn = 1 THEN name END AS name,
    number AS orders
FROM (
    SELECT 
        a.customer_id,
        a.name,
        b.number,
        ROW_NUMBER() OVER (PARTITION BY a.customer_id ORDER BY b.order_id) AS rn
    FROM tableA a
    JOIN tableB b ON a.customer_id = b.customer_id
    WHERE a.customer_id = 2
) t;

返回结果（应用层可将NULL显示为空）：

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