如何基于三张表编写SQL查询按购买类目数排序的Top客户

错误原因分析 #

你原来的SQL报错not a GROUP BY expression有两个核心问题：

1. GROUP BY 只指定了customer_id，但SELECT子句里的第二个子查询依赖orders.product_id，这个字段没有出现在GROUP BY里，也没有被聚合函数包裹，不符合SQL分组语法规范
2. 你的需求是统计每个客户购买的不同商品类目总数，原来的子查询写法只能统计单条订单对应商品的类目数，没法实现跨订单的类目去重计数

正确实现方案 #

直接用三表关联+分组聚合即可实现，SQL如下：

SELECT
    c.customer_name AS name,
    COUNT(DISTINCT p.category_id) AS purchased_category_count
FROM
    Orders o
INNER JOIN Customers c ON o.customer_id = c.customer_id
INNER JOIN Products p ON o.product_id = p.product_id
GROUP BY
    c.customer_id, c.customer_name
ORDER BY
    purchased_category_count DESC;

逻辑说明 #

• 先关联三张表，拿到每个订单对应的客户信息、商品所属类目信息
• 分组的时候同时按customer_id和customer_name分组，避免部分严格模式的数据库报错（customer_id是主键的情况下，两个字段关联分组逻辑上等价于只按customer_id分组）
• 用COUNT(DISTINCT p.category_id)统计每个客户购买的不同类目总数，避免同一个类目下多笔订单被重复计数
• 最后按类目总数倒序排序，排在最前面的就是购买类目最多的最优客户

如果你需要同时展示从未产生过购买行为的客户，可将INNER JOIN Customers改为LEFT JOIN Customers，同时把计数逻辑改为COUNT(DISTINCT IFNULL(p.category_id, 0))即可。

原有购买商品数排序SQL的优化建议 #

你已经实现的按购买商品数排序的查询也可以用关联写法优化，避免子查询带来的性能损耗：

SELECT
    c.customer_name AS name,
    COUNT(o.product_id) AS "number of ordered products"
FROM
    Orders o
INNER JOIN Customers c ON o.customer_id = c.customer_id
GROUP BY
    c.customer_id, c.customer_name
ORDER BY
    COUNT(o.product_id) DESC;

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