获取5个畅销品牌各3个畅销变体的MySQL查询问题

数据表结构 #

Product_variants（简称PV） #

CREATE TABLE `product_variants` (
  `id` int(11) UNSIGNED NOT NULL,
  `product_id` int(11) UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Products（简称P） #

CREATE TABLE `products` (
  `id` int(11) UNSIGNED NOT NULL,
  `brand_id` int(11) UNSIGNED DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Order_items（简称OI） #

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL,
  `order_id` int(11) UNSIGNED NOT NULL,
  `product_variant_id` int(11) UNSIGNED DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注：orders和brands表本次查询暂不需要

查询需求 #

获取15条数据，包含5个最畅销品牌，每个品牌取3个畅销变体，排序规则为：品牌销量降序 → 变体销量降序。预期结果如下：

product_variant_id    brand_id   number_brands_sold   number_variant_sold
1                     1          100                  50
2                     1          100                  30
3                     1          100                  10
4                     2          90                   40                      
5                     2          90                   25
6                     2          90                   10
7                     3          80                   45
8                     3          80                   20
9                     3          80                   5
10                    4          60                   25
11                    4          60                   20
12                    4          60                   8
13                    5          40                   10
14                    5          40                   9
15                    5          40                   8

兼容MySQL 5.x的解决方案 #

由于MySQL 5.x不支持窗口函数，需要通过用户变量实现分组取TopN的逻辑，完整查询语句如下：

SELECT
  final.product_variant_id,
  final.brand_id,
  final.number_brands_sold,
  final.number_variant_sold
FROM (
  SELECT
    var.product_variant_id,
    var.brand_id,
    brand_sales.number_brands_sold,
    var.number_variant_sold,
    -- 用变量标记每个品牌内的变体销量排名
    @row_num := CASE WHEN @current_brand = var.brand_id THEN @row_num + 1 ELSE 1 END AS row_num,
    @current_brand := var.brand_id
  FROM (
    -- 统计每个变体的销量并关联品牌ID，按品牌、变体销量降序排序
    SELECT
      pv.id AS product_variant_id,
      p.brand_id,
      COUNT(oi.id) AS number_variant_sold
    FROM order_items oi
    LEFT JOIN product_variants pv ON oi.product_variant_id = pv.id
    LEFT JOIN products p ON pv.product_id = p.id
    WHERE p.brand_id IS NOT NULL
    GROUP BY pv.id, p.brand_id
    ORDER BY p.brand_id, number_variant_sold DESC
  ) var
  -- 关联Top5品牌的总销量数据，过滤非目标品牌
  INNER JOIN (
    SELECT
      p.brand_id,
      COUNT(oi.id) AS number_brands_sold
    FROM order_items oi
    LEFT JOIN product_variants pv ON oi.product_variant_id = pv.id
    LEFT JOIN products p ON pv.product_id = p.id
    WHERE p.brand_id IS NOT NULL
    GROUP BY p.brand_id
    ORDER BY number_brands_sold DESC
    LIMIT 5
  ) brand_sales ON var.brand_id = brand_sales.brand_id
  -- 初始化排名变量
  CROSS JOIN (SELECT @current_brand := NULL, @row_num := 0) vars
  -- 按品牌总销量、变体销量降序排序，确保排名逻辑正确
  ORDER BY brand_sales.number_brands_sold DESC, var.number_variant_sold DESC
) final
-- 筛选每个品牌的Top3变体
WHERE final.row_num <= 3
ORDER BY final.number_brands_sold DESC, final.number_variant_sold DESC;

逻辑说明 #

1. 内层子查询先统计每个变体的销量并关联品牌ID，同时计算Top5品牌的总销量；
2. 通过用户变量@current_brand跟踪当前品牌，@row_num为每个品牌内的变体生成销量排名；
3. 最终筛选排名≤3的变体，再按品牌销量、变体销量降序输出结果。

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