MySQL中如何筛选每个客户购买量最高的商品？（8周SQL挑战）

要筛选每个客户purchase_rank=1的行，由于窗口函数的执行顺序晚于WHERE子句，无法直接在原查询的WHERE中过滤排名，需要将带排名的查询结果作为子查询或CTE（公共表表达式），再在外层筛选目标条件。同时注意MySQL的ONLY_FULL_GROUP_BY模式要求GROUP BY子句包含所有非聚合字段，需补充product_name。

方法1：使用CTE（MySQL 8.0及以上支持） #

WITH customer_purchase_ranks AS (
    SELECT
        customer_id,
        product_name,
        COUNT(sales.product_id) AS item_bought_count,
        DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY COUNT(sales.product_id) DESC) AS purchase_rank
    FROM sales
    JOIN menu ON sales.product_id = menu.product_id
    GROUP BY customer_id, sales.product_id, product_name
)
SELECT customer_id, product_name, item_bought_count
FROM customer_purchase_ranks
WHERE purchase_rank = 1;

方法2：使用子查询（兼容更低版本MySQL） #

SELECT customer_id, product_name, item_bought_count
FROM (
    SELECT
        customer_id,
        product_name,
        COUNT(sales.product_id) AS item_bought_count,
        DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY COUNT(sales.product_id) DESC) AS purchase_rank
    FROM sales
    JOIN menu ON sales.product_id = menu.product_id
    GROUP BY customer_id, sales.product_id, product_name
) AS ranked_purchases
WHERE purchase_rank = 1;

说明 #

• 两种方法都会保留每个客户购买次数最多的商品，若多个商品购买次数相同且均为最高（比如客户同时购买两种商品次数一样多），DENSE_RANK会给它们都标记为1，全部返回，符合“最受欢迎商品”的多结果场景。
• 修正GROUP BY子句添加product_name，避免ONLY_FULL_GROUP_BY模式下的语法错误。

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