如下學生表student，學生表中有姓名、分數、課程編號，需要按照課程對學生的成績進行排序
select * from jinbo.student;
id | name | score | course
----+-------+-------+--------
5 | elic | 70 | 1
4 | dock | 100 | 1
3 | cark | 80 | 1
2 | bob | 90 | 1
1 | alice | 60 | 1
10 | jacky | 80 | 2
9 | iris | 80 | 2
8 | hill | 60 | 1
7 | grace | 50 | 2
6 | frank | 70 | 2
6 | test | | 2
(11 rows)
1、rank over () 可以把成績相同的兩名是并列，如下course = 2 的結果rank值為：1 2 2 4 5
select name,
score,
course,
rank() over(partition by course order by score desc) as rank
from jinbo.student;
name | score | course | rank
-------+-------+--------+------
dock | 100 | 1 | 1
bob | 90 | 1 | 2
cark | 80 | 1 | 3
elic | 70 | 1 | 4
hill | 60 | 1 | 5
alice | 60 | 1 | 5
test | | 2 | 1
iris | 80 | 2 | 2
jacky | 80 | 2 | 2
frank | 70 | 2 | 4
grace | 50 | 2 | 5
(11 rows)
2、dense_rank()和rank over()很相似，可以把學生成績并列不間斷順序排名，如下course = 2 的結果rank值為：1 2 2 3 4
select name,score,
course,
dense_rank() over(partition by course order by score desc) as rank
from jinbo.student;
name | score | course | rank
-------+-------+--------+------
dock | 100 | 1 | 1
bob | 90 | 1 | 2
cark | 80 | 1 | 3
elic | 70 | 1 | 4
hill | 60 | 1 | 5
alice | 60 | 1 | 5
test | | 2 | 1
iris | 80 | 2 | 2
jacky | 80 | 2 | 2
frank | 70 | 2 | 3
grace | 50 | 2 | 4
(11 rows)
3、row_number 可以把相同成績的連續排名，如下 course = 2 的結果rank值為：1 2 3 4 5
select name,score,
course,
row_number() over(partition by course order by score desc) as rank
from jinbo.student;
name | score | course | rank
-------+-------+--------+------
dock | 100 | 1 | 1
bob | 90 | 1 | 2
cark | 80 | 1 | 3
elic | 70 | 1 | 4
hill | 60 | 1 | 5
alice | 60 | 1 | 6
test | | 2 | 1
iris | 80 | 2 | 2
jacky | 80 | 2 | 3
frank | 70 | 2 | 4
grace | 50 | 2 | 5
(11 rows)
使用rank over()的時候，空值是最大的，如果排序字段為null, 可能造成null字段排在最前面，影響排序結果，可以如下：
rank over(partition by course order by score desc nulls last)
4、總結
partition by 用于結果集分組，如果沒有指定，會把整個結果集作為一個分組
rank 、dense_rank 、row_numer 都是不同方式的結果集組內排序，一般都結合over 字句出現，over 字句里 會有 partition by、order by、last、first 的任意組合，如下：
rank() over(partition by a,b order by a, order by b desc);
rank() over(partition by a order by b nulls first)
rank() over(partition by a order by b nulls last)
補充：Oracle或者PostgreSQL的row_number over 排名語法
PostgreSQL 和Oracle 都提供了 row_number() over() 這樣的語句來進行對應的字段排名，很是方便。MySQL卻沒有提供這樣的語法。
這次我提供的表結構如下，
Table "ytt.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
i_name | character varying(10) | not null
rank | integer | not null
我模擬了20條數據來做演示。
t_girl=# select * from t1 order by i_name;
i_name | rank
---------+------
Charlie | 12
Charlie | 12
Charlie | 13
Charlie | 10
Charlie | 11
Lily | 6
Lily | 7
Lily | 7
Lily | 6
Lily | 5
Lily | 7
Lily | 4
Lucy | 1
Lucy | 2
Lucy | 2
Ytt | 14
Ytt | 15
Ytt | 14
Ytt | 14
Ytt | 15
(20 rows)
在PostgreSQL下，我們來對這樣的排名函數進行三種不同的執行方式1：
第一種：
完整的帶有排名字段以及排序。
t_girl=# select i_name,rank, row_number() over(partition by i_name order by rank desc) as rank_number from t1;
i_name | rank | rank_number
---------+------+-------------
Charlie | 13 | 1
Charlie | 12 | 2
Charlie | 12 | 3
Charlie | 11 | 4
Charlie | 10 | 5
Lily | 7 | 1
Lily | 7 | 2
Lily | 7 | 3
Lily | 6 | 4
Lily | 6 | 5
Lily | 5 | 6
Lily | 4 | 7
Lucy | 2 | 1
Lucy | 2 | 2
Lucy | 1 | 3
Ytt | 15 | 1
Ytt | 15 | 2
Ytt | 14 | 3
Ytt | 14 | 4
Ytt | 14 | 5
(20 rows)
第二種：
帶有完整的排名字段但是沒有排序。
t_girl=# select i_name,rank, row_number() over(partition by i_name ) as rank_number from t1;
i_name | rank | rank_number
---------+------+-------------
Charlie | 12 | 1
Charlie | 12 | 2
Charlie | 13 | 3
Charlie | 10 | 4
Charlie | 11 | 5
Lily | 6 | 1
Lily | 7 | 2
Lily | 7 | 3
Lily | 6 | 4
Lily | 5 | 5
Lily | 7 | 6
Lily | 4 | 7
Lucy | 1 | 1
Lucy | 2 | 2
Lucy | 2 | 3
Ytt | 14 | 1
Ytt | 15 | 2
Ytt | 14 | 3
Ytt | 14 | 4
Ytt | 15 | 5
(20 rows)
第三種：
沒有任何排名字段，也沒有任何排序字段。
t_girl=# select i_name,rank, row_number() over() as rank_number from t1;
i_name | rank | rank_number
---------+------+-------------
Lily | 7 | 1
Lucy | 2 | 2
Ytt | 14 | 3
Ytt | 14 | 4
Charlie | 12 | 5
Charlie | 13 | 6
Lily | 7 | 7
Lily | 4 | 8
Ytt | 14 | 9
Lily | 6 | 10
Lucy | 1 | 11
Lily | 7 | 12
Ytt | 15 | 13
Lily | 6 | 14
Charlie | 11 | 15
Charlie | 12 | 16
Lucy | 2 | 17
Charlie | 10 | 18
Lily | 5 | 19
Ytt | 15 | 20
(20 rows)
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- MYSQL row_number()與over()函數用法詳解
- PostgreSQL ROW_NUMBER() OVER()的用法說明
- postgreSQL中的row_number() 與distinct用法說明
- MySQL中row_number的實現過程
- SQL Server中row_number函數的常見用法示例詳解
- sql四大排名函數之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用介紹
- sql ROW_NUMBER()與OVER()方法案例詳解
