mysql自动排序函数dense_rank() over()、rank() over()、row_num() over()用法和区别

小咪咪 2023-01-01 03:41 162阅读 0赞

## 区别： ##

*  rank()：是并列排序，会跳过重复序号
 *  dense\_rank()：是并列排序，不会跳过重复序号
 *  row\_number()：是顺序排序，不跳过任何一个序号，就是行号

## 用法： ##

### 数据准备： ###

create table students(
    	id int(11)  auto_increment primary key,
    	name varchar(50) not null, 
    	score int(4) not null
    	);
    
    insert into students(name,score) values
    ('zhangsan', 100),
    ('lisi', 99),
    ('wangwu', 100), 
    ('trx', 90), 
    ('pjf', 99), 
    ('wzm', 96);

查看下插入的数据：

mysql> select * from students;
    +----+----------+-------+
    | id | name     | score |
    +----+----------+-------+
    |  1 | zhangsan |   100 |
    |  2 | lisi     |    99 |
    |  3 | wangwu   |   100 |
    |  4 | trx      |    90 |
    |  5 | pjf      |    99 |
    |  6 | wzm      |    96 |
    +----+----------+-------+

### 使用三种不同的方法进行排序： ###

select 
        id, 
        name, 
        rank() over(order by score desc) `rank`,
        row_number() over(order by score desc) `row_number`,
        dense_rank() over(order by score desc) `dense_rank`
    from students;
    
    --------------------------------- 结果 ------------------------------------
    +----+----------+-------+------+------------+------------+
    | id | name     | score | rank | row_number | dense_rank |
    +----+----------+-------+------+------------+------------+
    |  1 | zhangsan |   100 |    1 |          1 |          1 |
    |  3 | wangwu   |   100 |    1 |          2 |          1 |
    |  2 | lisi     |    99 |    3 |          3 |          2 |
    |  5 | pjf      |    99 |    3 |          4 |          2 |
    |  6 | wzm      |    96 |    5 |          5 |          3 |
    |  4 | trx      |    90 |    6 |          6 |          4 |
    +----+----------+-------+------+------------+------------+

## 那么，如何用一般的SQL实现这几个函数的效果呢？ ##

(1) rank()是并列排序，会跳过重复序号

思路：外查询按分数降序排列，子查询查出大于当前成绩的个数+1,个数+就是排名

select 
    	id, 
    	name, 
    	a.score,
    	(select count(score)+1 from students where score > a.score)  `rank`
    from students a
    order by score desc;
    --------------------------------- 结果 ------------------------------------
    +----+----------+-------+------+
    | id | name     | score | rank |
    +----+----------+-------+------+
    |  1 | zhangsan |   100 |    1 |
    |  3 | wangwu   |   100 |    1 |
    |  2 | lisi     |    99 |    3 |
    |  5 | pjf      |    99 |    3 |
    |  6 | wzm      |    96 |    5 |
    |  4 | trx      |    90 |    6 |
    +----+----------+-------+------+

(2) dense\_rank() 是并列排序，不会跳过重复序号

思路：用外查询按分数降序排列，子查询查出大于等于当前成绩去重后的个数，这个个数就是当前分数的名次。

select  
    	id,  
    	name, 
    	a.score, 
    	(select count(distinct score) from students where score >= a.score) as dense_rank 
    from students a
    order by score desc;
    --------------------------------- 结果 ------------------------------------
    +----+----------+-------+------------+
    | id | name     | score | dense_rank |
    +----+----------+-------+------------+
    |  1 | zhangsan |   100 |          1 |
    |  3 | wangwu   |   100 |          1 |
    |  2 | lisi     |    99 |          2 |
    |  5 | pjf      |    99 |          2 |
    |  6 | wzm      |    96 |          3 |
    |  4 | trx      |    90 |          4 |
    +----+----------+-------+------------+

(3) row\_number() 是顺序排序，不跳过任何一个序号，其实就是排序后的行号

思路：这种方式排序的关键在于如何获取行号，行号既是排名的名次

先看结论：

select  
    	id,  
    	name, 
    	a.score, 
    	(@rowNum:=@rowNum+1) as row_number 
    from students a,(select @rowNum:=0) b 
    order by score desc;
    --------------------------------- 结果 ------------------------------------
    +----+----------+-------+------------+
    | id | name     | score | row_number |
    +----+----------+-------+------------+
    |  1 | zhangsan |   100 |          1 |
    |  3 | wangwu   |   100 |          2 |
    |  2 | lisi     |    99 |          3 |
    |  5 | pjf      |    99 |          4 |
    |  6 | wzm      |    96 |          5 |
    |  4 | trx      |    90 |          6 |
    +----+----------+-------+------------+

**那么，如何获取行号？关键：自定义变量**@rowNum:=0;

假设有这样的数据

mysql> select id,name from students;
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | lisi     |
    |  3 | wangwu   |
    |  4 | trx      |
    |  5 | pjf      |
    |  6 | wzm      |
    +----+----------+

再看

mysql> SELECT @rowNum:=0;
    +------------+
    | @rowNum:=0 |
    +------------+
    |          0 |
    +------------+
    
    mysql> SELECT @rowNum:=1;
    +------------+
    | @rowNum:=1 |
    +------------+
    |          1 |
    +------------+

SELECT @rowNum:=0; 表示声明了一个叫rowNum的变量并赋值为0，这个变量名是自定义的，在sql中我们可以对这个变量进行计算，如：

mysql> select (@rowNum:=@rowNum+1) as num from (SELECT @rowNum:=0) a;
    +------+
    | num  |
    +------+
    |    1 |
    +------+
    
    mysql> select (@rowNum:=@rowNum+5) as num from (SELECT @rowNum:=0) a;
    +------+
    | num  |
    +------+
    |    5 |
    +------+

此时 (SELECT @rowNum:=0) a 相当于一个临时表，如果将它与students表进行连接将会得到：

mysql> select b.*,id,name from students a,(SELECT @rowNum:=0) b;
    +------------+----+----------+
    | @rowNum:=0 | id | name     |
    +------------+----+----------+
    |          0 |  1 | zhangsan |
    |          0 |  2 | lisi     |
    |          0 |  3 | wangwu   |
    |          0 |  4 | trx      |
    |          0 |  5 | pjf      |
    |          0 |  6 | wzm      |
    +------------+----+----------+

可以看到，b表只有一条数据，与a表连接后就有了a表的6条数据，如果我们对自定义变量@rowNum:进行+1操作，会有什么效果呢？

mysql> select (@rowNum:=@rowNum+1) as num,id,name from students a,(SELECT @rowNum:=0) b;
    +------+----+----------+
    | num  | id | name     |
    +------+----+----------+
    |    1 |  1 | zhangsan |
    |    2 |  2 | lisi     |
    |    3 |  3 | wangwu   |
    |    4 |  4 | trx      |
    |    5 |  5 | pjf      |
    |    6 |  6 | wzm      |
    +------+----+----------+
    6 rows in set (0.00 sec)

这就达到了查询结果带行号的目的，我们推演一下两个表连接的过程：

1.  一开始自定义变量@rowNum:=0，a表第一条数据(1,'zhangsan')与自定义变量join，不过我们取的是@rowNum:=@rowNum+1，即0+1，那么最终就会得到（1,1,'zhangsan'）
2.  此时自定义变量@rowNum:=1，a表第一条数据(2,'lisi')与自定义变量join,即1+1，那么最终得到(2,2,'lisi')
3.  ....(以此类推)

由此，我们就能得到sql查询带行号的结果。