一、经典面试题：

编写一个sql 查询来实现分数排名。
要求： 如果两个分数相同，则两个分数排名（Rank）相同。请注意：名词之间不应该有间隔。

分析

这道题目，看上去考察的是 dense_rank() over()函数的写法，实际上还要考虑 mysql版本号低于 8.x版本不支持自动排序函数时的情况，比如：公司常用的 mysql 版本号 为5.7.x版本 ，这个时候就要知道怎么手写sql实现 dense_rank() 函数的效果了。

查看mysql版本号

# 查看 mysql 版本号。 5.7.30...
select  VERSION()  from dual; 
1
2

二、准备数据

创建表

CREATE TABLE `score` (
  `Id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `Score` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT '分数',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='分数表';


INSERT INTO testdb.score(Id, Score) VALUES(1, '3.50');
INSERT INTO testdb.score(Id, Score) VALUES(2, '3.65');
INSERT INTO testdb.score(Id, Score) VALUES(3, '4.00');
INSERT INTO testdb.score(Id, Score) VALUES(4, '3.85');
INSERT INTO testdb.score(Id, Score) VALUES(5, '4.00');
INSERT INTO testdb.score(Id, Score) VALUES(6, '3.65');

1
2
3
4
5
6
7
8
9
10
11
12
13
14

三、Mysql 不同版本的 SQL 写法

3.1 Mysql 8.x 版本

# [mysql 8.x 版本以上，8.0也叫5.8版本] 支持排序函数( row_number()\ dense_rank()\ rank()  )和窗口函数 ( over() ) . 
select Score , dense_rank() over(order by s.score DESC) AS 'Rank' FROM  score s ;

-- select Score , row_number() over(order by s.score DESC) AS 'Rank' FROM  score s ;
-- select Score , rank() over(order by s.score DESC) AS 'Rank' FROM  score s ;
1
2
3
4
5

3.2 Mysql 5.x 版本

3.2.1 手动实现 dense_rank() over()函数效果

# [msyql 5.7.x版本]暂不支持窗口函数，只能手写。定义变量法

## 手动实现 dense_rank() over()函数的效果。dense_rank()在排序时，会把多个数值相同的归为一组，以同样的序号进行编号。但是他编号是连续的。
-- 1
-- 1
-- 2
-- 3
-- 3
-- 4
-- 思路：定义 两个变量： @Score 和"@dense_rank"。判断分数是否相等：如果分数相等，则"@dense_rank"相等；如果分数不等，"@dense_rank + 1"
select t.Score, t.Rank  from  (
	SELECT 	s.*,r.*,
	IF ( @Score = s.Score  , @dense_rank := @dense_rank , @dense_rank := @dense_rank + 1)  AS Rank,
	@Score :=s.Score    # 这里记得将子查询的值赋值
	from  score s , ( select @Score := NULL, @dense_rank := 0) r
	order by s.Score DESC
) t;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

3.2.2 手动实现 row_number() over()函数效果

## 【拓展】，其他函数的手写实现 如下
## 手动实现 row_number() over()函数的效果。ROW_NUMBER()排序的序号是连续不重复的，即使表中存在多个一样的数值仍然按顺序依次编号。 
-- 1
-- 2
-- 3
-- 4
-- 5
-- 思路： 定义 一个变量"@row_number"，初始值为0，每查询到一个结果，变量+1，并赋值给新的字段名 Rank
SELECT 	s.Score ,
	@row_number := @row_number + 1 AS Rank
from  score s , ( select @row_number := 0) r
order by s.Score DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.2.3 手动实现 rank() over()函数效果

## 手动实现 rank() over()函数的效果。 rank()在排序时，会把多个数值相同的归为一组，以同样的序号进行编号。但是编号不连续，会按照实际次序编辑下一组序号。
-- 思路：定义 三个变量： @Score 和"@rank" 和 @column 。先 @column + 1，然后判断分数是否相等：如果分数相等，则"rank"相等 ；如果分数不等，"@rank = @column"
-- 1
-- 1
-- 3
-- 4
-- 4
-- 6
select t.Score, t.Rank  from  (
	SELECT 	
		@column := @column + 1 ,   # 这里必须放到第一位，优先执行
		s.*,r.*,
		IF ( @Score = s.Score  , @rank := @rank ,  @rank := @column )  AS Rank,
		@Score :=s.Score    # 这里记得将子查询的值赋值
	from  score s , ( select @Score := NULL, @rank := 0, @column := 0) r
	order by s.Score DESC
) t;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19