Mysql——》group by分组后，添加行号

红太狼 2024-03-17 22:15 26阅读 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分组后，添加行号 ####

*  一、场景
 *  二、实现

## 一、场景 ##

**按user\_id分组，按分数和倒序排列，添加行号**

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

![image.png][]

## 二、实现 ##

SELECT
    	a.*,
    	@rn := @rn + 1 AS rn 
    FROM
    	( SELECT user_id, sum( score ) score FROM score WHERE 1 = 1 GROUP BY user_id ORDER BY score DESC ) a,(
    	SELECT
    	@rn := 0 
    	) AS b

![image.png][image.png 1]

[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
[image.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/6855bab0b1d44921a6d25d29fa9aff94.png
[image.png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/cde71069262a4089acac3295d771d16b.png