SQL排序之 row_number, rank(), dense_rank()区别

红太狼 2022-09-04 06:43 144阅读 0赞

## 三者的区别如下： ##

*  rank()排序相同时会重复，总数不变，即会出现1、1、3这样的排序结果；
 *  dense\_rank()排序相同时会重复，总数会减少，即会出现1、1、2这样的排序结果；
 *  row\_number()排序相同时不会重复，会根据顺序排序。

## 具体实例 ##

建表、插入数据

create table rownumber(
           id varchar(10) not null,
           name varchar(10) null,
           age varchar(10) null,
           salary int null
    );
    
    insert into rownumber(id,name,age,salary) values(1,'a',10,8000);
    insert into rownumber(id,name,age,salary) values(1,'a2',11,7500);
    insert into rownumber(id,name,age,salary) values(2,'b',12,7500);
    insert into rownumber(id,name,age,salary) values(2,'b2',13,4500);
    insert into rownumber(id,name,age,salary) values(3,'c',14,8000);
    insert into rownumber(id,name,age,salary) values(3,'c2',15,20000);
    insert into rownumber(id,name,age,salary) values(4,'d',16,30000);
    insert into rownumber(id,name,age,salary) values(5,'d2',17,8000);
    select * from rownumber;

![3aea3c0fdeefc1c1f36e8a6e7a109a82.png][]

数据

`row_number()`

select *, row_number() over(order by salary) as `rank` from rownumber;

![b159cfd78b6726a1011722d0e8315d3a.png][]

row\_number()排序结果

`rank()`

select *, rank() over(order by salary) as `rank` from rownumber;

![edaf8a0b3d3634fb6f57bebd75a1689f.png][]

rank()排序结果

`dense_rank()`

select *, dense_rank() over(order by salary) as `rank` from rownumber;

![c822b101afe08ae95e69841d16d8c3c6.png][]

dense\_rank()排序结果

## row\_number()的详细用法 ##

### 1.分组排序 ###

select *, row_number() over(partition by id order by salary) as `rank` from rownumber;

![5222f4fe061ad9c9d988b626478632e3.png][]

### 2. 每组内第二名的信息 ###

select * from (
    select *, row_number() over(partition by id order by salary) as `rank` from 
    rownumber) as temp
    where `rank` = 2;

![bbdda1c57216fa5f940100456a6a2586.png][]

### 3. 给年龄在13岁到16岁的数据按salary排名 ###

select *, row_number() over (order by salary) as `rank` from rownumber
    where age between 13 and 16;

![20a2bc4f5f07eb1a4d35ff9c453b0036.png][]

### 注： ###

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

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

--创建测试表
    create table te.sc(id int, name varchar(20),class varchar(20), score int);
    
    --给测试表插入数据
    insert into te.sc values (1,'张飞','一年一班',100);
    insert into te.sc values (2,'刘备','一年一班',99);
    insert into te.sc values (3,'李逵','一年一班',95);
    insert into te.sc values (4,'小动','一年一班',97);
    insert into te.sc values (5,'小智','一年一班',80);
    insert into te.sc values (6,'吕布','一年二班',67);
    insert into te.sc values (7,'赵云','一年二班',90);
    insert into te.sc values (8,'典韦','一年二班',89);
    insert into te.sc values (9,'关羽','一年二班',70);
    insert into te.sc values (10,'马超','一年二班',98);
    insert into te.sc values (11,'张媛','一年一班',100);

--列出每个班分数排名前三的学生
        select * from (select id, name, class, score , 
                    row_number() over (partition by class order by score desc) as r1,
                    rank() over (partition by class order by score desc) as r2 ,
                    dense_rank() over (partition by class order by score desc) as r3 from te.sc) B where r1<=3 ;
    
    id     name     class     score     r1   r2   r3
    1      张飞    一年一班    100        1    1    1
    11     张媛    一年一班    100        2    1    1
    2      刘备    一年一班    99         3    3    2
    10     马超    一年二班    98         1    1    1
    7      赵云    一年二班    90         2    2    2
    8      典韦    一年二班    89         3    3    3

这三个函数的区别主要在分数一致的情况下，row\_number()不重复排序，rank()重复且跳数字排序，dense\_rank()重复且不跳数字排序。

[3aea3c0fdeefc1c1f36e8a6e7a109a82.png]: /images/20220829/8fc57fbd13b74ead8a1a17eaaef03dbf.png
[b159cfd78b6726a1011722d0e8315d3a.png]: /images/20220829/c322c0946748492cbf1b1eb42c48b75f.png
[edaf8a0b3d3634fb6f57bebd75a1689f.png]: /images/20220829/6a7432eec1434fd09bc997955bd31b56.png
[c822b101afe08ae95e69841d16d8c3c6.png]: /images/20220829/488cdd98246c4f31b5cc17f21ef1c7b9.png
[5222f4fe061ad9c9d988b626478632e3.png]: /images/20220829/70d0d000494945ba8bdc952fc797f73f.png
[bbdda1c57216fa5f940100456a6a2586.png]: /images/20220829/c1c56b0248ac4f259a50f361f2b1b395.png
[20a2bc4f5f07eb1a4d35ff9c453b0036.png]: /images/20220829/d6833c9f22434c1ab5ba9b594e7146a1.png