SQL查询：如何获取每个国家销量前10的商品并按国家名、销量排序

原代码错误点 #

• 分组逻辑错误：仅按国家ID分组，未同时按商品维度分组，无法统计单个商品的销量，且select *不符合SQL分组语法规范
• 排序逻辑错误：默认使用升序排序，无法优先展示高销量商品
• 取数逻辑错误：全局limit 10仅能返回所有国家的前10条数据，无法实现「每个国家取TOP10」的需求

可行实现方案（支持MySQL8.0+、PostgreSQL、Oracle等主流支持窗口函数的数据库） #

WITH country_item_sales AS (
    SELECT 
        c.name AS country_name,
        i.id AS item_id,
        i.name AS item_name,
        COUNT(r.id) AS sales_volume,
        -- 按国家分区，同一国家内按商品销量倒序排名
        ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY COUNT(r.id) DESC) AS sales_rank
    FROM item AS i
    INNER JOIN items_receipts AS ir ON i.id = ir.item_id
    INNER JOIN receipt AS r ON ir.receipt_id = r.id
    INNER JOIN country AS c ON r.country_id = c.id
    GROUP BY c.id, c.name, i.id, i.name
)
SELECT country_name, item_id, item_name, sales_volume
FROM country_item_sales
WHERE sales_rank <= 10
-- 结果按国家名称升序、同国家内销量倒序排序
ORDER BY country_name ASC, sales_volume DESC;

方案说明 #

• 如果需要保留销量并列的商品，可以将ROW_NUMBER()替换为RANK()或者DENSE_RANK()
• 若使用不支持窗口函数的低版本MySQL，可使用关联子查询的方式实现，但性能远低于窗口函数方案

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