如何在SQL INNER JOIN结果中仅显示每个客户的最受欢迎商品？

你的基础查询已经正确算出了每个客户对各商品的购买次数，要筛选出每个客户购买次数最多的商品，可以用窗口函数来实现，这是处理这类"分组取top N"问题的常用方法。

方法1：使用RANK()窗口函数（保留并列第一的情况） #

这个方法会保留像客户B那样多个商品购买次数相同且都是最高的情况：

WITH customer_product_counts AS (
    SELECT 
        sales.customer_id, 
        menu.product_name, 
        COUNT(menu.product_name) AS Times_purchased
    FROM menu
    INNER JOIN sales
        ON menu.product_id = sales.product_id
    GROUP BY sales.customer_id, menu.product_name
)
SELECT customer_id, product_name, Times_purchased
FROM (
    SELECT 
        *,
        RANK() OVER (PARTITION BY customer_id ORDER BY Times_purchased DESC) AS purchase_rank
    FROM customer_product_counts
) ranked_counts
WHERE purchase_rank = 1;

代码解释 #

1. 首先用CTE（customer_product_counts）复用你已经写好的查询，得到每个客户各商品的购买次数；
2. 然后在子查询里用RANK()窗口函数：
   • PARTITION BY customer_id：按客户分组，每个客户单独计算排名；
   • ORDER BY Times_purchased DESC：按购买次数从高到低排序，次数最高的排第1；
3. 最后筛选出purchase_rank = 1的记录，就是每个客户购买次数最多的商品。

为什么用RANK()而不是ROW_NUMBER()？ #

• ROW_NUMBER()会给每个并列的记录分配不同的序号（比如客户B的三个商品会被标为1、2、3），最终只会保留一个；
• RANK()会给并列的记录分配相同的排名（客户B的三个商品都会是1），符合你需要保留所有最受欢迎商品的需求；
• 如果需要连续排名（比如并列第一后下一个是2而不是4），可以用DENSE_RANK()，效果和RANK()在这个场景里一致。

方法2：不使用CTE（适合不支持CTE的旧版本SQL） #

如果你的SQL环境不支持CTE，可以把基础查询作为子查询嵌套：

SELECT customer_id, product_name, Times_purchased
FROM (
    SELECT 
        sales.customer_id, 
        menu.product_name, 
        COUNT(menu.product_name) AS Times_purchased,
        RANK() OVER (PARTITION BY sales.customer_id ORDER BY COUNT(menu.product_name) DESC) AS purchase_rank
    FROM menu
    INNER JOIN sales
        ON menu.product_id = sales.product_id
    GROUP BY sales.customer_id, menu.product_name
) ranked_counts
WHERE purchase_rank = 1;

执行结果 #

运行上面的代码后，你会得到：

这正是你需要的每个客户最受欢迎的商品（包括并列的情况）。

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