关于MySQL中分组查询并取每组前N条数据的解决方案
原创2025/1/9大约 2 分钟
关于MySQL中分组查询并取每组前N条数据的解决方案
1、业务背景
现有一张商品表（t_goods），其 SQL 脚本如下：
CREATE TABLE `t_goods`
(
`goods_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品ID',
`shop_id` int(11) NOT NULL COMMENT '店铺ID',
`goods_name` varchar(255) NOT NULL COMMENT '商品名称',
`goods_price` decimal(10, 2) NOT NULL COMMENT '商品价格',
PRIMARY KEY (`goods_id`)
) AUTO_INCREMENT = 11 COMMENT '商品表';其数据初始化脚本如下：
INSERT INTO `t_goods` (`goods_id`, `shop_id`, `goods_name`, `goods_price`) VALUES (1, 1, '小炒黄牛肉', 35.00);
INSERT INTO `t_goods` (`goods_id`, `shop_id`, `goods_name`, `goods_price`) VALUES (2, 2, '辣椒炒肉', 15.00);
INSERT INTO `t_goods` (`goods_id`, `shop_id`, `goods_name`, `goods_price`) VALUES (3, 1, '地三鲜盖饭', 16.90);
INSERT INTO `t_goods` (`goods_id`, `shop_id`, `goods_name`, `goods_price`) VALUES (4, 1, '锅包肉', 68.00);
INSERT INTO `t_goods` (`goods_id`, `shop_id`, `goods_name`, `goods_price`) VALUES (5, 2, '黄焖鸡米饭', 25.00);
INSERT INTO `t_goods` (`goods_id`, `shop_id`, `goods_name`, `goods_price`) VALUES (6, 3, '油泼面', 11.00);
INSERT INTO `t_goods` (`goods_id`, `shop_id`, `goods_name`, `goods_price`) VALUES (7, 3, '家常刀削面', 10.00);
INSERT INTO `t_goods` (`goods_id`, `shop_id`, `goods_name`, `goods_price`) VALUES (8, 3, '西红柿鸡蛋炒面', 12.00);
INSERT INTO `t_goods` (`goods_id`, `shop_id`, `goods_name`, `goods_price`) VALUES (9, 2, '砂锅米线', 17.00);
INSERT INTO `t_goods` (`goods_id`, `shop_id`, `goods_name`, `goods_price`) VALUES (10, 4, '老干妈炒饭', 13.00);最终的测试数据如下图：
2、需求目标
分页查询每家店铺中价格最高的前N（此处举例N=2）个商品信息，且只查询 shop_id 为 1、2、3 的店铺的商品。
3、解决方案
3.1、方案一：利用 group by + group_concat() 函数
SELECT
shop_id,
SUBSTRING_INDEX( GROUP_CONCAT( goods_id ORDER BY goods_price DESC ), ',', N ) AS goods_ids
FROM
t_goods
WHERE
shop_id IN ( 1, 2, 3 )
GROUP BY
shop_id;查询结果如下：
💡释义：先通过 group_concat() 函数将每个店铺下的商品ID进行拼接，后用 substring_index（）函数取前N个。
⚠️注意：此方案无法满足上文中的需求目标，无法实现分页，且 group_concat() 有长度限制，默认1024，可通过 SHOW VARIABLES LIKE 'group_concat_max_len'; 查询当前设置大小。
3.2、方案二：利用 MySQL 8.0 之后的 ROW_NUMBER() 和 OVER 窗口函数
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER ( PARTITION BY shop_id ORDER BY goods_price DESC ) AS rn
FROM t_goods
WHERE shop_id IN ( 1, 2, 3 )
) AS t
WHERE t.rn <= N查询结果如下：
💡释义：通过 over( partition by shop_id order by goods_price desc ) 可以对数据按照价格排序后再根据店铺分组，并使用 row_number() 给每一组中的数据加上序号，最后通过 where 条件对序号进行过滤，即可达到取前几条或后几条的效果。
⚠️注意：此方案可以满足上文中的需求目标，可以实现分页，但是，以上的窗口函数在 MySQL 8.0.2 版本开始才进行的引入！