如何用子查询在单列中展示多条订单商品结果

原SQL存在的问题 #

• 子查询返回多行结果，无法直接作为单个列值，会触发Subquery returns more than 1 row错误
• 表连接逻辑错误：INNER JOIN customer on customer.customer_id= customer.id条件无效，应关联orders.customer_id和customer.id
• 未关联Product表却引用product.price，会导致表不存在的错误
• 使用SUM聚合函数但未添加GROUP BY，无法按订单分组计算总额

修正方案：按订单聚合商品信息 #

根据不同数据库类型，用对应的字符串聚合函数将同一订单的商品（带数量）合并为单个字段，支持换行或分隔符形式：

1. MySQL 版本（支持换行或逗号分隔） #

SELECT
    o.id,
    o.order_number,
    c.name AS customer,
    c.address,
    SUM(op.qty * p.price) AS total,
    -- 用换行符分隔每个商品，替换CHAR(10)为', '可改为逗号分隔
    GROUP_CONCAT(CONCAT(op.qty, 'x ', p.name) SEPARATOR CHAR(10)) AS products
FROM orders o
INNER JOIN customer c ON o.customer_id = c.id
INNER JOIN order_products op ON o.id = op.order_id
INNER JOIN product p ON op.product_id = p.id
GROUP BY o.id, o.order_number, c.name, c.address
ORDER BY o.created_at;

2. PostgreSQL 版本 #

SELECT
    o.id,
    o.order_number,
    c.name AS customer,
    c.address,
    SUM(op.qty * p.price) AS total,
    -- 用换行符分隔，替换为', '可改为逗号分隔
    STRING_AGG(CONCAT(op.qty, 'x ', p.name), E'\n') AS products
FROM orders o
INNER JOIN customer c ON o.customer_id = c.id
INNER JOIN order_products op ON o.id = op.order_id
INNER JOIN product p ON op.product_id = p.id
GROUP BY o.id, o.order_number, c.name, c.address
ORDER BY o.created_at;

3. SQL Server 版本 #

SELECT
    o.id,
    o.order_number,
    c.name AS customer,
    c.address,
    SUM(op.qty * p.price) AS total,
    -- 用换行符分隔，替换为', '可改为逗号分隔
    STRING_AGG(CONCAT(op.qty, 'x ', p.name), CHAR(10)) AS products
FROM orders o
INNER JOIN customer c ON o.customer_id = c.id
INNER JOIN order_products op ON o.id = op.order_id
INNER JOIN product p ON op.product_id = p.id
GROUP BY o.id, o.order_number, c.name, c.address
ORDER BY o.created_at;

说明 #

• 聚合后的products字段会将同一订单的所有商品按数量x商品名格式合并，用指定分隔符（换行或逗号）分隔
• 确保所有表连接条件正确，且GROUP BY包含所有非聚合字段，符合数据库分组规则

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