腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

Mysql常用函數(shù)之Rank排名函數(shù)詳解

更新時(shí)間：2024年01月04日 10:37:34 作者：pan_mlpan

這篇文章主要介紹了Mysql常用函數(shù)之Rank排名函數(shù)詳解,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

mysql中的排名函數(shù)

主要介紹一下mysql里面的排名函數(shù)，涉及到的函數(shù)有以下幾個(gè)：

rank()、dense_rank()、row_number()

1、準(zhǔn)備工作

建立一個(gè)rank表：

create table rank(
	id int(10) not null primary key,
	name varchar(20) not null,
	score int(10) not null
);

插入一些數(shù)據(jù)：

insert into rank values(1,'a',100);
insert into rank values(2,'b',100);
insert into rank values(3,'c',95);
insert into rank values(4,'d',95);
insert into rank values(5,'e',95);
insert into rank values(6,'a',90);
insert into rank values(7,'a',89);

表及數(shù)據(jù)的截圖：

2、rank() 函數(shù)

語法結(jié)構(gòu)：

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

按照某字段的排序結(jié)果添加排名，但它是跳躍的、間斷的排名

（1）若按照分?jǐn)?shù)直接進(jìn)行排序的話，例如按照score進(jìn)行排名

兩個(gè)并列第一名后，下一個(gè)是第三名。

SELECT score, rank() over(ORDER BY score desc) as 'Rank' 
FROM rank;

結(jié)果：

+------+---------+
| score|   Rank  |
+------+---------+
|  100 |       1 |
|  100 |       1 |
|  95  |       3 |
|  95  |       3 |
|  95  |       3 |
|  90  |       6 |
|  89  |       7 |
+------+---------+
7 rows in set (0.02 sec)

（2）若按照某個(gè)字段分區(qū)進(jìn)行排序的話，例如按照name進(jìn)行分區(qū)，根據(jù)分?jǐn)?shù)進(jìn)行排名：

SELECT name , 
	score ,
	rank() over(partition by name ORDER BY score desc) as 'Rank' 
	FROM rank;

首先，PARTITION BY子句按姓名將結(jié)果集分成多個(gè)分區(qū)。

然后，ORDER BY子句按分?jǐn)?shù)對(duì)結(jié)果集進(jìn)行排序。

結(jié)果：

+------+------+---------+
| name | score|   Rank  |
+------+------+---------+
|  a   |  100 |       1 |
|  a   |  90  |       2 |
|  a   |  89  |       3 |
|  b   |  100 |       1 |
|  c   |  95  |       1 |
|  d   |  95  |       1 |
|  e   |  95  |       1 |
+------+------+---------+
7 rows in set (0.02 sec)

3、row_number() 函數(shù)

MySQL ROW_NUMBER()從8.0版開始引入了功能。這ROW_NUMBER()是一個(gè)窗口函數(shù)或分析函數(shù)，它為從1開始應(yīng)用的每一行分配一個(gè)序號(hào)

語法結(jié)構(gòu)如下：

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

例如還是根據(jù)分?jǐn)?shù)進(jìn)行排序

SELECT 
 row_number() OVER (
 ORDER BY score
 ) row_num,
 score
 FROM rank;

結(jié)果：

+-------+------+---------+
|row_num| score|   Rank  |
+------ +------+---------+
|  1    |  100 |       1 |
|  2    |  100 |       2 |
|  3    |  95  |       3 |
|  4    |  95  |       1 |
|  5    |  95  |       1 |
|  6    |  90  |       1 |
|  7    |  89  |       1 |
+-------+------+---------+
7 rows in set (0.02 sec)

其次，使用ROW_NUMBER()函數(shù)將行劃分為所有列的分區(qū)。對(duì)于每個(gè)唯一的行集，將重新開始行號(hào)。

SELECT 
    id,
    name,
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS row_num
FROM rank;

結(jié)果：

+------+------+---------+
| id   | name | row_num |
+------+------+---------+
|    1 | a    |       1 |
|    2 | a    |       2 |
|    3 | a    |       3 |
|    4 | b    |       1 |
|    5 | c    |       1 |
|    6 | d    |       1 |
|    7 | e    |       1 |
+------+------+---------+
7 rows in set (0.02 sec)

4、dense_rank() 函數(shù)

dense 英語中指“稠密的、密集的”。dense_rank()是的排序數(shù)字是連續(xù)的、不間斷。當(dāng)有相同的分?jǐn)?shù)時(shí)，它們的排名結(jié)果是并列的，例如，1,2,2,3。

語法結(jié)構(gòu)：

DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

例如，還是根據(jù)成績進(jìn)行排名：

SELECT score, dense_rank() over(ORDER BY score desc) as 'Rank' 
FROM rank;

結(jié)果：

+------+---------+
| score|   Rank  |
+------+---------+
|  100 |       1 |
|  100 |       1 |
|  95  |       2 |
|  95  |       2 |
|  95  |       2 |
|  90  |       3 |
|  89  |       4 |
+------+---------+
7 rows in set (0.02 sec)

若按照某個(gè)字段分區(qū)進(jìn)行排序的話，例如按照name進(jìn)行分區(qū)，根據(jù)分?jǐn)?shù)進(jìn)行排名

SELECT name , 
	score ,
	dense_rank() over(partition by name ORDER BY score desc) as 'Rank' 
	FROM rank;

首先，PARTITION BY子句按姓名將結(jié)果集分成多個(gè)分區(qū)。

然后，ORDER BY子句按分?jǐn)?shù)對(duì)結(jié)果集進(jìn)行排名。

結(jié)果：

+------+------+---------+
| name | score|   Rank  |
+------+------+---------+
|  a   |  100 |       1 |
|  a   |  90  |       2 |
|  a   |  89  |       3 |
|  b   |  100 |       1 |
|  c   |  95  |       1 |
|  d   |  95  |       1 |
|  e   |  95  |       1 |
+------+------+---------+
7 rows in set (0.02 sec)

這數(shù)據(jù)可能不太明顯，如果再插入一條數(shù)據(jù)：

insert into rank values(8,'a',90);

然后查詢，結(jié)果如下，與rank函數(shù)執(zhí)行的結(jié)果就可以看到區(qū)別了：

+------+------+---------+
| name | score|   Rank  |
+------+------+---------+
|  a   |  100 |       1 |
|  a   |  90  |       2 |
|  a   |  90  |       2 |
|  a   |  89  |       3 |
|  b   |  100 |       1 |
|  c   |  95  |       1 |
|  d   |  95  |       1 |
|  e   |  95  |       1 |
+------+------+---------+
7 rows in set (0.02 sec)