rank()、row_number()、dense_rank()用法详解

建表测试

测试表数据：test1

• CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_test` /*!40100 DEFAULT CHARACTER SET utf8 */;

• USE `db_test`; /*Table structure for table `test1` */

• DROP TABLE IF EXISTS `test1`;

• CREATE TABLE `test1` ( `id` int(10) NOT NULL, `score` int(20) DEFAULT NULL, `class` char(10) COLLATE utf8_bin DEFAULT NULL, `name` char(20) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

• /*Data for the table `test1` */

• insert into `test1`(`id`,`score`,`class`,`name`) values (1,100,'语文','张三'),(2,98,'语文','李四'),(3,98,'语文','王五'),(4,98,'数学','张三'),(5,96,'数学','李四'),(6,92,'数学','王五'),(7,85,'数学','张三'),(8,96,'语文','张三'),(9,96,'语文','张三'),(10,91,'语文','张三'),(11,91,NULL,'张三');

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

• row_number()：当分数相同的时候还会依次排序，排名不同，正常排序。即1，2，3。
• rank()：当分数相同的时候不会依次排序，相同的分数排名相同，跳跃排序，即1，1，3。
• dense_rank()：当分数相同的时候不会依次排序，相同的分数排名相同，连续排序，即1，1，2。

总结：row_number()不重复排序，rank()重复且跳数字排序，dense_rank()重复且不跳数字排序。

全局排序和分组排序

 （1）全局排序（不加partition by）

-- MySQL8语法，row_number()、rank()、dense_rank()不分组排序对照
SELECT *, 
row_number() over(ORDER BY T.score) AS `row_number`,
rank() over(ORDER BY T.score) AS `rank`,
dense_rank() over(ORDER BY T.score) AS `dense_rank` 
FROM test1 T;

查询结果：

 （2）分组排序（加上partition by）

-- MySQL8语法，row_number()、rank()、dense_rank()分组排序对照
 
SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY T.class ORDER BY T.score DESC) AS `row_number`,
RANK() OVER(PARTITION BY T.class ORDER BY T.score DESC) AS `rank`,
DENSE_RANK() OVER(PARTITION BY T.class ORDER BY T.score DESC) AS `dense_rank` 
FROM test1 T

查询结果： 

 

 注意：

1、当score没有相同的，那么rank()和row_number()效果一样
2、如果表数据中有null，则判断的时候需要用 <=>来判断是否相等，因为null不能用=判断      select null = null    输出为null，不对，select null <=> null才返回1，对的。  

应用实例

1、row_number()

  （1）查询每门课程第二名的信息

SELECT * FROM (
SELECT *, row_number() over(PARTITION BY T.class ORDER BY T.score DESC) AS `rank` FROM 
test1 T) AS temp
WHERE `rank` = 2;

（2）给id在1到6的学习按分数排名

SELECT *, row_number() over (ORDER BY T.score) AS `rank` 
FROM test1 T
WHERE T.id BETWEEN 1 AND 6;

 

注意：

row_number函数得到的列别名可用于order by 排序，因为order by执行在select之后。

where, group by, having都不可引用该列，因为这些语句执行在select之前，此时函数尚未计算出值。