MySQL中多种排名实现

已于 2023-12-16 00:01:24 修改
阅读量4.9k 收藏 36
点赞数 8
分类专栏： 数据库 文章标签： mysql 数据库 java
于 2023-02-01 16:20:59 首次发布
程序员Forlan
本文链接：https://blog.csdn.net/qq_36433289/article/details/128676858
版权

文章目录

前言

我们主要以MySQL5.7和8为例，分别实现了多种排名，唯一连续排名、并列连续排名、并列不连续排名，演示了分组和不分组的情况，基本已经涵盖了所有情况了。

一、准备数据

新建一张表forlan_score

CREATE TABLE `forlan_score` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `student_name` varchar(255) DEFAULT NULL COMMENT '学生名称',
  `score` int(20) DEFAULT '-1' COMMENT '分数',
	`course_name` varchar(255) DEFAULT NULL COMMENT '课程',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='学生成绩表';

插入数据

INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (1, '小明', 70, '数学');
INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (2, '小红', 65, '英语');
INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (3, '小林', 100, '数学');
INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (4, '小黄', 100, '语文');
INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (5, '小东', 80, '语文');
INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (6, '小美', 90, '英语');
INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (7, '小伟', 88, '英语');
INSERT INTO `test`.`forlan_score` (`id`, `student_name`, `score`, `course_name`) VALUES (8, '小小', 100, '数学');

二、实现排名（不分组）

1、不重复，连续排名

1）mysql5.7实现

  • 使用自定义变量（外部sql）
SET @cur_rank := 0;
SELECT
	student_name,
	score,
	@cur_rank := @cur_rank + 1 AS ranking 
FROM
	forlan_score 
ORDER BY
	score DESC;
  • 使用自定义变量（内部sql）（推荐）
SELECT
	fs.student_name,
	fs.score,
	( @cur_rank := @cur_rank + 1 ) AS ranking 
FROM
	forlan_score fs,
	( SELECT @cur_rank := 0 ) r 
ORDER BY
	score DESC;

2）mysql8实现

ROW_NUMBER()

SELECT
	student_name,
	score,
	ROW_NUMBER() OVER ( ORDER BY score DESC ) AS ranking 
FROM
	forlan_score;

3）效果

+--------------+-------+---------+
| student_name | score | ranking |
+--------------+-------+---------+
| 小林         |   100 |       1 |
| 小黄         |   100 |       2 |
| 小小         |   100 |       3 |
| 小美         |    90 |       4 |
| 小伟         |    88 |       5 |
| 小东         |    80 |       6 |
| 小明         |    70 |       7 |
| 小红         |    65 |       8 |
+--------------+-------+---------+

2、并列，连续排名

1）mysql5.7实现

  • 使用自定义变量 + IF
SELECT
	fs.student_name,
	fs.score,
	IF( @pre_score = fs.score, @cur_rank, @cur_rank := @cur_rank + 1 ) AS ranking,
	@pre_score := fs.score 
FROM
	forlan_score fs,( SELECT @cur_rank := 0, @pre_score := NULL ) r 
ORDER BY
	fs.score DESC;
  • 使用自定义变量 + CASE WHEN
SELECT
	fs.student_name,
	fs.score,
	(
		CASE
			WHEN @pre_score = fs.score THEN @cur_rank 
			WHEN @pre_score := fs.score THEN @cur_rank := @cur_rank + 1 
		END 
	) AS ranking 
	FROM
		forlan_score fs,(SELECT @cur_rank := 0,@pre_score := NULL) r 
ORDER BY
	fs.score DESC;

2）mysql8实现

DENSE_RANK()

SELECT
	student_name,
	score,
	DENSE_RANK() OVER ( ORDER BY score DESC ) AS ranking 
FROM
	forlan_score;

3）效果

+--------------+-------+---------+
| student_name | score | ranking |
+--------------+-------+---------+
| 小林         |   100 |       1 |
| 小黄         |   100 |       1 |
| 小小         |   100 |       1 |
| 小美         |    90 |       2 |
| 小伟         |    88 |       3 |
| 小东         |    80 |       4 |
| 小明         |    70 |       5 |
| 小红         |    65 |       6 |
+--------------+-------+---------+

3、并列，不连续排名

1）mysql5.7实现

  • 使用自定义变量 + IF
SELECT
	fs.student_name,
	fs.score,
	@row_num := @row_num + 1,
	IF( @pre_score = fs.score, @cur_rank, @cur_rank := @row_num ) AS ranking,
	@pre_score := fs.score 
FROM
	forlan_score fs,
	(SELECT @cur_rank := 0,@pre_score := NULL,@row_num := 0 ) r 
ORDER BY
	fs.score DESC;
  • 使用自定义变量 + CASE WHEN
SELECT
	fs.student_name,
	fs.score,
	@row_num := @row_num + 1,
	( CASE WHEN @pre_score = fs.score THEN @cur_rank WHEN @pre_score := fs.score THEN @cur_rank := @row_num END ) AS ranking 
FROM
	forlan_score fs,
	( SELECT @cur_rank := 0, @pre_score := NULL, @row_num := 0 ) r 
ORDER BY
	fs.score DESC;

2）mysql8实现

RANK()

SELECT
	student_name,
	score,
	RANK() OVER ( ORDER BY score DESC ) AS ranking 
FROM
	forlan_score;

3）效果

+--------------+-------+---------+
| student_name | score | ranking |
+--------------+-------+---------+
| 小林         |   100 |       1 |
| 小黄         |   100 |       1 |
| 小小         |   100 |       1 |
| 小美         |    90 |       4 |
| 小伟         |    88 |       5 |
| 小东         |    80 |       6 |
| 小明         |    70 |       7 |
| 小红         |    65 |       8 |
+--------------+-------+---------+

三、实现按照课程分组排名

1、不重复，连续排名

1）mysql5.7实现

  • 使用自定义变量 + IF
SELECT
	fs.student_name,
	fs.course_name,
	fs.score,
	IF(@cur_couse = course_name, @cur_rank := @cur_rank+1, @cur_rank :=1) AS ranking,
	@cur_couse := fs.course_name
FROM
	forlan_score fs,
	( SELECT @cur_rank := 0,  @cur_couse := NULL ) r 
ORDER BY
	fs.course_name,fs.score DESC;

2）mysql8实现

ROW_NUMBER()

SELECT
	student_name,
	course_name,
	score,
	ROW_NUMBER() OVER (PARTITION BY course_name ORDER BY course_name,score DESC) AS ranking
FROM
	forlan_score;

3）效果

+--------------+-------------+-------+---------+
| student_name | course_name | score | ranking |
+--------------+-------------+-------+---------+
| 小林         | 数学        |   100 |       1 |
| 小小         | 数学        |   100 |       2 |
| 小明         | 数学        |    70 |       3 |
| 小美         | 英语        |    90 |       1 |
| 小伟         | 英语        |    88 |       2 |
| 小红         | 英语        |    65 |       3 |
| 小黄         | 语文        |   100 |       1 |
| 小东         | 语文        |    80 |       2 |
+--------------+-------------+-------+---------+

2、并列，连续排名

1）mysql5.7实现

  • 使用自定义变量 + IF
SELECT
	fs.student_name,
	fs.course_name,
	fs.score,
	IF(@cur_couse = course_name, IF( @pre_score = fs.score, @cur_rank, @cur_rank := @cur_rank+1 ), @cur_rank :=1) AS ranking,
	@pre_score := fs.score,
	@cur_couse := fs.course_name
FROM
	forlan_score fs,
	( SELECT @cur_rank := 0, @pre_score := NULL, @cur_couse := NULL ) r 
ORDER BY
	fs.course_name,fs.score DESC;

2）mysql8实现

DENSE_RANK()

SELECT
	student_name,
	course_name,
	score,
	DENSE_RANK() OVER (PARTITION BY course_name ORDER BY course_name,score DESC) AS ranking
FROM
	forlan_score;

3）效果

+--------------+-------------+-------+---------+
| student_name | course_name | score | ranking |
+--------------+-------------+-------+---------+
| 小林         | 数学        |   100 |       1 |
| 小小         | 数学        |   100 |       1 |
| 小明         | 数学        |    70 |       2 |
| 小美         | 英语        |    90 |       1 |
| 小伟         | 英语        |    88 |       2 |
| 小红         | 英语        |    65 |       3 |
| 小黄         | 语文        |   100 |       1 |
| 小东         | 语文        |    80 |       2 |
+--------------+-------------+-------+---------+

3、并列，不连续排名

1）mysql5.7实现

  • 使用自定义变量 + IF
SELECT
	fs.student_name,
	fs.course_name,
	fs.score,
	IF(@cur_couse = course_name, @row_num := @row_num + 1, @row_num :=1),
	IF(@cur_couse = course_name, IF( @pre_score = fs.score, @cur_rank, @cur_rank := @row_num ),@cur_rank :=1) AS ranking,
	@pre_score := fs.score,
	@cur_couse := fs.course_name
FROM
	forlan_score fs,
	( SELECT @cur_rank := 0, @pre_score := NULL, @row_num := 0,@cur_couse := NULL ) r 
ORDER BY
	fs.course_name,fs.score DESC;

2）mysql8实现

RANK()

SELECT
	student_name,
	course_name,
	score,
	RANK() OVER (PARTITION BY course_name ORDER BY course_name,score DESC) AS ranking
FROM
	forlan_score;

3）效果

+--------------+-------------+-------+---------+
| student_name | course_name | score | ranking |
+--------------+-------------+-------+---------+
| 小林         | 数学        |   100 |       1 |
| 小小         | 数学        |   100 |       1 |
| 小明         | 数学        |    70 |       3 |
| 小美         | 英语        |    90 |       1 |
| 小伟         | 英语        |    88 |       2 |
| 小红         | 英语        |    65 |       3 |
| 小黄         | 语文        |   100 |       1 |
| 小东         | 语文        |    80 |       2 |
+--------------+-------------+-------+---------+
程序员Forlan
关注
专栏目录
MySQL实现排名
yanhanhui1的博客
03-08 849
排名在我们日常生活中并不陌生，例如玩游戏的分数等等排名，都需要用到SQL语句查询数据库。不过具体怎么通过SQL语句实现排名呢？ 为了举例说明，我先随便定义一个表和一些数据，以便于下面的查询： create table `playertable` ( `id` bigint not null, `nickname` varchar(32) not null, `age` int not null, `score` int not null, primary key (`id`) ) engin
[SQL] MySQL实现多种排名查询
進撃の算法
07-23 3131
示例： Scores 表如下： +----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+ SQL架构：点击此处 一、普通排名（排名无并列） 按成绩排序后的...
MySQL实现排名------实例
大佬云集技术答疑交流群：743262921(进群暗号:222)
11-24 1378
MySQL实现排名 一、排名 新MySQL8.0推出RANK排名函数，完全支持排名这种需求，但是必须MySQL8.0 以上版本才支持这个特性 那么8.0以下的版本有什么方法实现呢？ 方法：使用用户变量，记录名次 用户自定义变量：以"@“开始，形式为”@name"，以区分用户变量及列名。它可以是任何随机的，复合的标量表达式，只要其中没有列指定 例如： 使用用户变量 select @a:=1 m,@b:=@a+1 n; 执行结果：mn12 符号 " := " 是赋值的意思，与Java、Python等编程语
mysql实现排名
weixin_44919041的博客
02-10 3114
1.mysql常用的排名函数 mysql在8.0提供了三个函数进行排名，RANK、DENSE_RANK和ROW_NUMBER函数，如果低于mysql8.0需要自己实现 2.RANK、DENSE_RANK和ROW_NUMBER使用 1.rank、dense_rank、row_number函数的使用 （1）创建表语句 CREATE TABLE `score_ranking` ( `rank_id` va...
MySQL-排名函数ROW_NUMBER()，RANK()，DENSE_RANK()函数的异同
最新发布
GreyWings的博客
09-22 808
MySQL-排名函数ROW_NUMBER()，RANK()，DENSE_RANK()函数的异同 假设有如下表结构与数据，class_id表示班级，需求：现在要按照班级分组，每个班级的学生进行年龄从小到大排序。
Mysql 常见排名实现
小发菜
11-07 790
记录Mysql排名实现的不同方法，主要有排名函数row_number()、rank()、dense_rank()和变量的实现。
mysql 计算排名_教你用SQL实现统计排名
weixin_30210749的博客
02-02 1452
前言：?在某些应用场景中，我们经常会遇到一些排名的问题，比如按成绩或年龄排名。排名也有多种排名方式，如直接排名、分组排名，排名有间隔或排名无间隔等等，这篇文章将总结几种MySQL中常见的排名问题。创建测试表create table scores_tb (id int auto_increment primary key,xuehao int not null,score int not null...
mysql sql并列排名_教你用SQL实现统计排名
weixin_32724679的博客
01-27 1511
前言：在某些应用场景中，我们经常会遇到一些排名的问题，比如按成绩或年龄排名。排名也有多种排名方式，如直接排名、分组排名，排名有间隔或排名无间隔等等，这篇文章将总结几种MySQL中常见的排名问题。创建测试表create table scores_tb (id int auto_increment primary key,xuehao int not null,score int not null)...
thinkphp+mysql中英文伪静态企业网站、完整无BUG源码
04-01
【标题】"thinkphp+mysql中英文伪静态企业网站、完整无BUG源码"涉及到的主要技术包括ThinkPHP框架、MySQL数据库以及伪静态技术，这些在构建一个高效、稳定且易于SEO优化的企业级网站中扮演着关键角色。 ThinkPHP是...
基于springboot+vue+MySQL实现的在线考试系统+源代码+文档
06-13
在这个项目中，MySQL用于存储考试相关的信息，包括但不限于试题、答案、考生信息、成绩记录等。Spring Boot与MySQL通过JPA（Java Persistence API）或MyBatis等ORM框架进行交互，使得数据库操作更加简便和高效。 **...
mysql 查询排名，包括并列排名和连续排名
m0_72642319的博客
06-24 1494
在MySQL中，根据不同的需求，查询排名可以分为并列排名和连续排名两种情况。
mysql制作排行榜_mysql实现排行榜
weixin_33162074的博客
01-26 598
博主新人一枚，大家可以提出自己的宝贵意见。下来我们进入正题。前两天在给app写接口时，需要实现一个排行榜的业务。当然我们首先要了解排行榜的业务需求是什么，其次才是接口实现，不能像我一样，走了不少的弯路。大家首先要了解接口的场景，再就是排行榜的规则，我们这里说的中国式排行榜。排行榜总结了一下分为3种：中国式排行： 非中国式排行1： 非中国式排行2：1； ...
MySql并列排名
whyalwaysmea
05-17 9513
业务背景 数据排名是很常用的功能，简单的排名功能可以根据order by来实现，但是如果数据一样，排名应该并列的时候，order by虽然是排序的，但是名次却不是并列的。 我们先通过order by演示一下。 建表 CREATE TABLE `user_score` ( `user_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户i...
mysql中的排名
Java领域优秀创作者
03-25 1661
本文详细介绍了在MySQL 5.7和MySQL 8.0中实现排名的方法。MySQL 5.7通过自连接和聚合函数实现排名，而MySQL 8.0通过窗口函数简化了这一过程。对于需要实现复杂排名的情况，MySQL 8.0的窗口函数提供了更强大的功能和更高的效率。MySQL 5.7需要复杂的自连接和聚合函数。SQL语句较长，易读性较差。性能相对较低。MySQL 8.0窗口函数简化了排名实现。SQL语句简洁，易读性好。性能优越，特别是对于大数据量的操作。
MySQL（四）：使用SQL实现排名
欧阳方超的专栏
08-15 1463
RANK()与ROW_NUMBER()和DENSE_RANK()的区别在于，ROW_NUMBER()为每一行分配唯一的排名，而DENSE_RANK()则不会跳过排名。
排名分数 mysql
09-21
根据提供的引用内容，要计算MySQL中分数的排名可以有多种方法。其中一种方法是使用临时变量和联查来实现。首先，可以通过以下查询语句来获取分数对应的排名： ``` SELECT @Rank := @Rank + 1 AS Rank, s.Score FROM (SELECT @Rank := 0) m, (SELECT Score FROM Scores GROUP BY Score DESC) s; ``` 然后，可以将这个查询结果与原始的Scores表进行联查，并根据排名字段进行排序，得到最终的结果： ``` SELECT s.Score, r.Rank FROM Scores s LEFT JOIN ( SELECT @Rank := @Rank + 1 AS Rank, s.Score FROM (SELECT @Rank := 0) m, (SELECT Score FROM Scores GROUP BY Score DESC) s ) r ON s.Score = r.Score ORDER BY r.Rank; ``` 这样就可以得到按照分数排名的结果。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序员Forlan

你的鼓励将是我创作的最大动力！

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值