SQL查询：按供应商总销排序 取各供应商Top3热销产品

基础信息 #

现有三类业务数据表：

• 销售表：存储全量销售记录
• 供应商表：存储供应商基础信息
• 产品表：存储产品基础信息

需求规则 #

需要编写SQL返回如下结果：

1. 所有供应商各自销量排名前3的产品，以及对应供应商售卖该产品的总销售额
2. 结果排序逻辑：
   • 一级排序：按供应商整体总销售额从高到低排列，高销售额供应商的3条产品记录整体排在前面
   • 二级排序：同一供应商下的产品按单品销售额从高到低排列
3. 所有供应商必须展示，每个供应商固定返回3条热销产品记录

示例场景：供应商包含Mike、Lucas、Amy、Bob、Matt、Agatha，共10款在售产品，预期输出格式参考：

• Mike - cereal - $400
• Mike - juice - $100
• Mike - soap - $50
• Amy - soap - $200
• Amy - lettuce - $150
• Amy - cheese - $100
• 其余供应商按规则依次排列...

现存问题 #

当前编写的SQL仅能返回全量产品的聚合销售结果，无法实现单供应商取Top3、按供应商总销售额排序的效果，且存在语法笔误（v.vendor_name后误写为点号，应为逗号），原代码如下：

SELECT v.vendor_name, p.product_name, sum(f.total) as total
FROM vendors v, sales f, products p
WHERE v.id_vendor = f.vendor and p.id_product = f.product
GROUP BY v.vendor_name, p.product_name
ORDER BY total DESC

实现方案 #

使用窗口函数实现需求，适用于MySQL 8.0+、PostgreSQL、SQL Server、Oracle等所有支持标准窗口函数的数据库版本，代码如下：

WITH vendor_product_agg AS (
    -- 聚合得到每个供应商对应每个单品的销售额，同时计算供应商整体总销售额
    SELECT
        v.vendor_name,
        p.product_name,
        SUM(f.total) AS product_sales,
        SUM(SUM(f.total)) OVER (PARTITION BY v.vendor_name) AS vendor_total_sales
    FROM vendors v
    INNER JOIN sales f
        ON v.id_vendor = f.vendor
    INNER JOIN products p
        ON p.id_product = f.product
    GROUP BY v.vendor_name, p.product_name
),
product_rank AS (
    -- 给每个供应商下的产品按销售额倒序排名
    SELECT
        vendor_name,
        product_name,
        product_sales,
        vendor_total_sales,
        ROW_NUMBER() OVER (PARTITION BY vendor_name ORDER BY product_sales DESC) AS rn
    FROM vendor_product_agg
)
-- 过滤每个供应商Top3产品，按要求排序输出
SELECT
    CONCAT(vendor_name, ' - ', product_name, ' - $', product_sales) AS result
FROM product_rank
WHERE rn <= 3
ORDER BY vendor_total_sales DESC, product_sales DESC;

逻辑说明 #

• 改用显式JOIN语法替代原隐式逗号连接，避免隐式连接可能产生的笛卡尔积问题，可读性更高
• 嵌套使用聚合函数+窗口函数SUM(SUM(f.total)) OVER (PARTITION BY v.vendor_name)，一次聚合同时得到单品销售额和供应商总销售额，减少表关联次数
• 使用ROW_NUMBER()实现分组内排名，如果业务上需要支持同销售额产品并列排名，可替换为RANK()或DENSE_RANK()函数
• 最终排序严格按照需求规则，先按供应商总销售额倒序，再按单品销售额倒序

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