一、数据库表结构以及数据

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

1.2、效果

+--------------+-------+---------+

| student_name | score | ranking |

+--------------+-------+---------+

| 小林         |   100 |       1 |

| 小黄         |   100 |       2 |

| 小小         |   100 |       3 |

| 小美         |    90 |       4 |

| 小伟         |    88 |       5 |

| 小东         |    80 |       6 |

| 小明         |    70 |       7 |

| 小红         |    65 |       8 |

+--------------+-------+---------+

2、并列排名，连续

2.1、不同版本实现

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;

2.2、效果

+--------------+-------+---------+

| student_name | score | ranking |

+--------------+-------+---------+

| 小林         |   100 |       1 |

| 小黄         |   100 |       1 |

| 小小         |   100 |       1 |

| 小美         |    90 |       2 |

| 小伟         |    88 |       3 |

| 小东         |    80 |       4 |

| 小明         |    70 |       5 |

| 小红         |    65 |       6 |

+--------------+-------+---------+

3、并列排名，不连续

3.1、不同版本实现

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.2、效果

+--------------+-------+---------+

| student_name | score | ranking |

+--------------+-------+---------+

| 小林         |   100 |       1 |

| 小黄         |   100 |       1 |

| 小小         |   100 |       1 |

| 小美         |    90 |       4 |

| 小伟         |    88 |       5 |

| 小东         |    80 |       6 |

| 小明         |    70 |       7 |

| 小红         |    65 |       8 |

+--------------+-------+---------+

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

1、不重复，连续

1.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;

1.2、效果

+--------------+-------------+-------+---------+

| student_name | course_name | score | ranking |

+--------------+-------------+-------+---------+

| 小林         | 数学        |   100 |       1 |

| 小小         | 数学        |   100 |       2 |

| 小明         | 数学        |    70 |       3 |

| 小美         | 英语        |    90 |       1 |

| 小伟         | 英语        |    88 |       2 |

| 小红         | 英语        |    65 |       3 |

| 小黄         | 语文        |   100 |       1 |

| 小东         | 语文        |    80 |       2 |

+--------------+-------------+-------+---------+

2、并列排名，连续

2.1、不同版本实现

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;

2.2、效果

+--------------+-------------+-------+---------+

| student_name | course_name | score | ranking |

+--------------+-------------+-------+---------+

| 小林         | 数学        |   100 |       1 |

| 小小         | 数学        |   100 |       1 |

| 小明         | 数学        |    70 |       2 |

| 小美         | 英语        |    90 |       1 |

| 小伟         | 英语        |    88 |       2 |

| 小红         | 英语        |    65 |       3 |

| 小黄         | 语文        |   100 |       1 |

| 小东         | 语文        |    80 |       2 |

+--------------+-------------+-------+---------+

3、并列排名，不连续

3.1、不同版本实现

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.2、效果

+--------------+-------------+-------+---------+

| student_name | course_name | score | ranking |

+--------------+-------------+-------+---------+

| 小林         | 数学        |   100 |       1 |

| 小小         | 数学        |   100 |       1 |

| 小明         | 数学        |    70 |       3 |

| 小美         | 英语        |    90 |       1 |

| 小伟         | 英语        |    88 |       2 |

| 小红         | 英语        |    65 |       3 |

| 小黄         | 语文        |   100 |       1 |

| 小东         | 语文        |    80 |       2 |

+--------------+-------------+-------+---------+

最新文章

  1. (2016弱校联盟十一专场10.3) B.Help the Princess!
  2. struts之动态方法调用使用通配符
  3. 【WP 8.1开发】文件选取器的使用方法
  4. 【CSS】使用CSS选择器
  5. [No000013]在Office中关闭自动拼写检查和自动语法检查
  6. Linux iostat字段解析
  7. webpack我遇到的一些坑
  8. Cardinality Feedback
  9. 版本控制器 （Svn，Git）
  10. html5新增标签兼容性
  11. mac jdbc连接mysql
  12. 在C语言控制台程序中播放MP3音乐
  13. Android 的Google+平台
  14. Apache的安装
  15. lesson - 15 Linux系统日常管理4
  16. RabbitMQ Linux(Redhat6.5)安装（二 ）
  17. python --- 冒泡排序算法
  18. codeforces / project Euler 泛做
  19. LeetCode题解41.First Missing Positive
  20. django AnonymousUser

热门文章

  1. 沁恒CH32V003(二): Ubuntu20.04 MRS和Makefile开发环境配置
  2. 网页状态码(HTTP状态码)。
  3. Flutter 构建windows应用
  4. 详解从浏览器地址栏输入URL到页面显示的步骤
  5. C#使用不安全指针来操作bitmap
  6. 读 Clean Code，关于变量命名和可维护代码
  7. 使用vite + vue3 + ant-design-vue + vue-router + vuex 创建一个后台管理应用
  8. .NET 6 实现滑动验证码（二）、基本数据
  9. 【大数据面试】【框架】Hadoop-入门、HDFS
  10. python random模块几个常用方法