多关联表求和：查询客户采购总额及采购商品列表

嘿，看你之前用GROUP BY关联表的时候踩了统计错误的坑，还不想用PHP多次查询，想用1-2个SQL搞定需求？完全没问题，我给你针对性的解决方案：

首先，你的需求其实可以用一个SQL直接生成和示例格式一致的结果，核心是用字符串聚合函数把每个客户的采购商品拼接起来，同时准确计算总采购额。下面分不同数据库给出实现：

MySQL版本 #

SELECT
    c.customer_name AS CUSTOMER,
    GROUP_CONCAT(si.item_name ORDER BY si.item_name SEPARATOR ' ') AS ITEMS,
    SUM(si.price) AS `TOTAL VALUE`
FROM customers c
LEFT JOIN sales s ON c.id = s.customer_id
LEFT JOIN sale_items si_rel ON s.id = si_rel.sale_id
LEFT JOIN stock_items si ON si_rel.stock_item_id = si.id
GROUP BY c.id, c.customer_name
ORDER BY `TOTAL VALUE` DESC;

关键点说明： #

• 用LEFT JOIN是为了确保即使没有任何采购记录的客户也能被列出来，如果只需要统计有采购的客户，换成INNER JOIN就行
• GROUP_CONCAT里加ORDER BY si.item_name让商品自动按字母排序，SEPARATOR ' '用空格分隔，完美匹配你要的示例格式
• SUM(si.price)直接累加每个采购商品的价格，不会出现统计错误——因为每个sale_items条目对应一个商品的单价，求和就是最准确的总采购额

PostgreSQL版本 #

如果你的数据库是PostgreSQL，只需要把MySQL的GROUP_CONCAT换成PostgreSQL的STRING_AGG函数：

SELECT
    c.customer_name AS CUSTOMER,
    STRING_AGG(si.item_name, ' ' ORDER BY si.item_name) AS ITEMS,
    SUM(si.price) AS "TOTAL VALUE"
FROM customers c
LEFT JOIN sales s ON c.id = s.customer_id
LEFT JOIN sale_items si_rel ON s.id = si_rel.sale_id
LEFT JOIN stock_items si ON si_rel.stock_item_id = si.id
GROUP BY c.id, c.customer_name
ORDER BY "TOTAL VALUE" DESC;

为啥之前GROUP BY会出错？ #

大概率是你关联表的时候没处理好一对多的关系——比如直接关联sales和sale_items后就分组，可能会因为一个订单对应多个商品导致重复统计订单数据，但我们这里直接基于每个商品的单价求和，从根源上避免了重复统计的问题。

如果确实需要拆成两个SQL分别实现两个需求，也可以这么搞：

需求1：仅按总采购额排序的客户列表

SELECT
    c.customer_name,
    SUM(si.price) AS totalSales
FROM customers c
LEFT JOIN sales s ON c.id = s.customer_id
LEFT JOIN sale_items si_rel ON s.id = si_rel.sale_id
LEFT JOIN stock_items si ON si_rel.stock_item_id = si.id
GROUP BY c.id, c.customer_name
ORDER BY totalSales DESC;

需求2：每个客户的所有采购商品（重复购买的重复显示，按字母排序）

SELECT
    c.customer_name,
    si.item_name
FROM customers c
LEFT JOIN sales s ON c.id = s.customer_id
LEFT JOIN sale_items si_rel ON s.id = si_rel.sale_id
LEFT JOIN stock_items si ON si_rel.stock_item_id = si.id
ORDER BY c.customer_name, si.item_name;

这个SQL会每行显示一个客户的一个采购商品，重复购买的商品会重复出现，整体按客户名称和商品字母顺序排列。

内容的提问来源于stack exchange，提问作者Derick Van Wyk