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

曾经终败给现在 2024-04-08 09:53 83阅读 0赞

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

#### Mysql——》group by分组后，取每组的前n条数据 ####

*  一、场景
 *  二、实现
 *   *  方法一
     *  方法二
     *  方法三

## 一、场景 ##

`按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);

![在这里插入图片描述][aa3e0f588f1b4e2b98c2f9f6fe40107b.png]

## 二、实现 ##

### 方法一 ###

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;

![在这里插入图片描述][b3ad3dba2c864d86856b010d611d4dac.png]

### 方法二 ###

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;

![在这里插入图片描述][073f036fc59a42b8a57e34fa25e75f2f.png]

### 方法三 ###

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

![在这里插入图片描述][7bd67a45731d49f58b75d36fd6fc63e6.png]

[Java]: https://blog.csdn.net/weixin_43453386/article/details/84788317
[Mysql]: https://blog.csdn.net/weixin_43453386/article/details/88667709
[Redis]: https://blog.csdn.net/weixin_43453386/article/details/127966762
[Kafka]: https://blog.csdn.net/weixin_43453386/article/details/128189472
[Spring]: https://blog.csdn.net/weixin_43453386/article/details/124900806
[SpringBoot]: https://blog.csdn.net/weixin_43453386/article/details/84788714
[MyBatis_MyBatis-Plus]: https://blog.csdn.net/weixin_43453386/article/details/84788053
[Linux]: https://blog.csdn.net/weixin_43453386/article/details/89241912
[MongoDB]: https://blog.csdn.net/weixin_43453386/article/details/84788450
[Elasticsearch]: https://blog.csdn.net/weixin_43453386/article/details/108583782
[aa3e0f588f1b4e2b98c2f9f6fe40107b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/c3be8f8811cf4e409df9019b502189b6.png
[b3ad3dba2c864d86856b010d611d4dac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/94991e42004941e29e5a20c66f6dd444.png
[073f036fc59a42b8a57e34fa25e75f2f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/7b08739d05e14e1fadbe11af549cb5a5.png
[7bd67a45731d49f58b75d36fd6fc63e6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/1d2a2ba01f7b4cd092cb50a57bda1110.png