MYSQL中rank()、row_number()和dense_rank()的区别

一、准备

首先我们根据下列建表语句简历scores表

1
2
3
4
5
CREATE TABLE `scores` (
	`id` INT KEY,
	`score` VARCHAR ( 8 ),
    `subject` VARCHAR ( 8 ) 
)

然后插入几条数据

1
2
3
4
5
6
7
8
INSERT INTO `scores`
VALUES
	( 1, 3.50, '语文' ),
	( 2, 3.65, '数学' ),
	( 3, 4.00, '英语' ),
	( 4, 3.85, '语文' ),
	( 5, 4.00, '数学' ),
	( 6, 3.65, '英语' );

二、rank()函数

语法结构：

1
2
3
4
RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

这是一个专门用来排名的函数，通过PARTITION BY进行分组，然后使用ORDER BY进行排序，ORDER BY和常规用法一样。

现在我们使用刚才建立的表进行一个成绩排名，如下：

1
2
3
4
5
6
SELECT
	`subject`,
	score,
	RANK() over ( PARTITION BY `subject` ORDER BY score DESC ) AS 'score_rank'
FROM
	`scores`

结果如下：

subject score score_rank
数学 4.00 1
数学 3.65 2
英语 4.00 1
英语 3.65 2
语文 3.85 1
语文 3.50 2

这里我们使用rank()函数进行分数排名，我们使用PARTITION BY subject将数据通过学科分组，然后ORDER BY score DESC对成绩进行从高到低的排名。并且在row_number()和dense_rank()，PARTITION BY 和 ORDER BY 的用法相同。

但是如果我们不进行分组只对分数进行排名呢？如下所示

1
2
3
4
5
SELECT
	score,
	RANK() over ( ORDER BY score DESC ) AS 'score_rank'
FROM
	`scores`

结果如下：

score score_rank
4.00 1
4.00 1
3.85 3
3.65 4
3.65 4
3.50 6

这里，我们可以发现，PARTITION BY是可以不写的，不写则不进行分组，row_number()和dense_rank()同理。

但是观察rank()排名的结果，我们发现，出现相同成绩时，两个并列第一名后，下一个是第三名,rank()是跳跃的、间断的排名

三、row_number()函数

因为在这里PARTITION BY分组用法相同，且没有太大研究性，所以下面将不再使用

ROW_NUMBER()从MYSQL 8.0版开始引入的功能，如果低于这个版本，请跳过

row_number()语法如下

1
2
3
4
ROW_NUMBER() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]

和RANK() 用法一样

还是对分数进行排序

1
2
3
4
5
6
SELECT
	id,
	score,
	ROW_NUMBER() over ( ORDER BY score DESC ) AS 'score_rank' 
FROM
	`scores`

结果如下

id score score_rank
3 4.00 1
5 4.00 2
4 3.85 3
2 3.65 4
6 3.65 5
1 3.50 6

观察可以发现，row_number（）函数的排名是连贯的，即使出现相同的分数也分了一二名，那么他凭什么给相同分数分排名高低呢？也许你看到了前面的id规律，其实不然，这里的id只是方便你观察，毕竟有些人会拿字符串当id，所以按id分高低是不严谨的。其实row_number() 会给每一行按照数据库中的顺序，从1开始给每一行分配一个序号，在这里和我们的id恰好相同

四、dense_rank()

语法如下：

1
2
3
4
5
DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

和前者区别不大，直接分数排名

1
2
3
4
5
SELECT
	score,
	DENSE_RANK() over ( ORDER BY score DESC ) AS 'score_rank' 
FROM
	`scores`

结果如下：

score score_rank
4.00 1
4.00 1
3.85 2
3.65 3
3.65 3
3.50 4

可以观察得到，dense_rank()的结果中的排序数字是连续的、不间断。当有相同的分数时，它们的排名结果是并列的