如何在多表JOIN的SQL查询中获取每组前5条数据

首先你的初始查询有两个明显问题：

• SQL语法顺序错误：ORDER BY必须放在GROUP BY之后，否则数据库会报错
• 分组逻辑矛盾：按customerId分组却同时选择itemId，这不符合标准SQL的分组规则（除非用特定数据库的非标准模式），你实际需要的应该是按customerId, itemId分组，统计每个客户购买各商品的次数

要实现分组后取每组前5条的需求，确实需要用到窗口函数（PARTITION BY）结合行号筛选，下面是具体实现步骤：

步骤1：完成JOIN与基础统计 #

先把两张表关联，统计每个客户每个商品的购买次数：

SELECT 
    o.customerId, 
    oi.itemId, 
    COUNT(oi.itemId) AS num
FROM Orders o
JOIN OrderItems oi ON o.orderId = oi.orderId
GROUP BY o.customerId, oi.itemId

这里给表加别名（o和oi），避免orderId字段的歧义。

步骤2：用窗口函数添加组内行号 #

在上面的查询基础上，用ROW_NUMBER()窗口函数，按customerId分区，每个分区内按购买次数num倒序排序，生成组内行号：

SELECT 
    customerId,
    itemId,
    num,
    ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY num DESC) AS row_rank
FROM (
    SELECT 
        o.customerId, 
        oi.itemId, 
        COUNT(oi.itemId) AS num
    FROM Orders o
    JOIN OrderItems oi ON o.orderId = oi.orderId
    GROUP BY o.customerId, oi.itemId
) t

步骤3：筛选每组前5条记录 #

最后在外层查询中，筛选row_rank <=5的记录，得到每个客户购买次数前5的商品：

SELECT customerId, itemId, num
FROM (
    SELECT 
        customerId,
        itemId,
        num,
        ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY num DESC) AS row_rank
    FROM (
        SELECT 
            o.customerId, 
            oi.itemId, 
            COUNT(oi.itemId) AS num
        FROM Orders o
        JOIN OrderItems oi ON o.orderId = oi.orderId
        GROUP BY o.customerId, oi.itemId
    ) t
) tt
WHERE row_rank <= 5
ORDER BY customerId, row_rank

补充说明 #

• 如果你的数据库是Oracle，也可以用ROWNUM结合子查询，但ROW_NUMBER()的方式更通用直观
• 若存在购买次数相同的商品，ROW_NUMBER()会给它们分配不同行号；如果需要并列排名（比如相同次数的都算前5），可以换成RANK()或DENSE_RANK()函数

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