Mysql——》group by分组后，在每个组内取前n条数据

小仙。

已于 2023-07-19 15:00:48 修改

阅读量1k

点赞数

分类专栏： Mysql 文章标签： mysql 分组 group by 每组前几条

于 2022-08-18 18:03:13 首次发布

本文链接：https://blog.csdn.net/weixin_43453386/article/details/126411135

版权

Mysql 专栏收录该内容

105 篇文章 5 订阅

订阅专栏

推荐链接：
    总结——》【Java】
    总结——》【Mysql】
    总结——》【Redis】
    总结——》【Kafka】
    总结——》【Spring】
    总结——》【SpringBoot】
    总结——》【MyBatis、MyBatis-Plus】
    总结——》【Linux】
    总结——》【MongoDB】
    总结——》【Elasticsearch】

一、场景

按course_id分组，按score倒序排列，每个组内取前2个score

CREATE TABLE `score` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int NOT NULL COMMENT '用户ID',
  `course_id` int NOT NULL COMMENT '课程ID',
  `score` int NOT NULL COMMENT '分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


INSERT INTO `xiaoxian`.`score` (`id`, `user_id`, `course_id`, `score`) VALUES (1, 1, 1, 90);
INSERT INTO `xiaoxian`.`score` (`id`, `user_id`, `course_id`, `score`) VALUES (2, 1, 2, 70);
INSERT INTO `xiaoxian`.`score` (`id`, `user_id`, `course_id`, `score`) VALUES (3, 1, 3, 88);
INSERT INTO `xiaoxian`.`score` (`id`, `user_id`, `course_id`, `score`) VALUES (4, 2, 2, 90);
INSERT INTO `xiaoxian`.`score` (`id`, `user_id`, `course_id`, `score`) VALUES (5, 2, 3, 66);
INSERT INTO `xiaoxian`.`score` (`id`, `user_id`, `course_id`, `score`) VALUES (6, 2, 4, 92);
INSERT INTO `xiaoxian`.`score` (`id`, `user_id`, `course_id`, `score`) VALUES (7, 3, 1, 99);
INSERT INTO `xiaoxian`.`score` (`id`, `user_id`, `course_id`, `score`) VALUES (8, 3, 3, 90);
INSERT INTO `xiaoxian`.`score` (`id`, `user_id`, `course_id`, `score`) VALUES (9, 4, 4, 88);

在这里插入图片描述

二、实现

方法一

select
* 
from
  (
   select
    @rn:= case when @course_id = a.course_id then @rn + 1 else 1 end as rn
    ,@course_id:= course_id as course_id
    ,score
   from(select * from score x order by course_id,score desc) a
  ) c
where rn <= 2;

在这里插入图片描述

方法二

SELECT
	a.course_id,
	a.score,
	count( b.course_id ) rn 
FROM
	score a
	LEFT JOIN score b ON a.course_id = b.course_id 
	AND a.score < b.score 
GROUP BY
	a.course_id,
	a.score 
HAVING
	count( b.course_id ) < 2 
ORDER BY
	a.course_id,
	a.score DESC;

在这里插入图片描述

方法三

SELECT
	a.course_id,
	a.score,
	count( yy ) rn 
FROM
	(
	SELECT
		a.course_id,
		a.score,
		b.course_id xx,
		b.score yy 
	FROM
		score a
		LEFT JOIN score b ON a.course_id = b.course_id 
		AND a.score < b.score 
	) a 
GROUP BY
	a.course_id,
	a.score 
HAVING
	rn < 2 
ORDER BY
	a.course_id,
	a.score DESC