前言：
在某些應(yīng)用場景中，我們經(jīng)常會(huì)遇到一些排名的問題，比如按成績或年齡排名。排名也有多種排名方式，如直接排名、分組排名，排名有間隔或排名無間隔等等，這篇文章將總結(jié)幾種MySQL中常見的排名問題。
創(chuàng)建測試表
create table scores_tb (
id int auto_increment primary key,
xuehao int not null,
score int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into scores_tb (xuehao,score) values (1001,89),(1002,99),(1003,96),(1004,96),(1005,92),(1006,90),(1007,90),(1008,94);
# 查看下插入的數(shù)據(jù)
mysql> select * from scores_tb;
+----+--------+-------+
| id | xuehao | score |
+----+--------+-------+
| 1 | 1001 | 89 |
| 2 | 1002 | 99 |
| 3 | 1003 | 96 |
| 4 | 1004 | 96 |
| 5 | 1005 | 92 |
| 6 | 1006 | 90 |
| 7 | 1007 | 90 |
| 8 | 1008 | 94 |
+----+--------+-------+
1.普通排名
按分?jǐn)?shù)高低直接排名，從1開始，往下排，類似于row number。下面我們給出查詢語句及排名結(jié)果。
# 查詢語句
SELECT xuehao, score, @curRank := @curRank + 1 AS rank
FROM scores_tb, (
SELECT @curRank := 0
) r
ORDER BY score desc;
# 排序結(jié)果
+--------+-------+------+
| xuehao | score | rank |
+--------+-------+------+
| 1002 | 99 | 1 |
| 1003 | 96 | 2 |
| 1004 | 96 | 3 |
| 1008 | 94 | 4 |
| 1005 | 92 | 5 |
| 1006 | 90 | 6 |
| 1007 | 90 | 7 |
| 1001 | 89 | 8 |
+--------+-------+------+
上述查詢語句中，我們申明了一個(gè)變量 @curRank ,并將此變量初始化為0，查得一行將此變量加一，并以此作為排名。我們看到這類排名是沒間隔的并且有些分?jǐn)?shù)相同但排名不同。
2.分?jǐn)?shù)相同，名次相同，排名無間隔
# 查詢語句
SELECT xuehao, score,
CASE
WHEN @prevRank = score THEN @curRank
WHEN @prevRank := score THEN @curRank := @curRank + 1
END AS rank
FROM scores_tb,
(SELECT @curRank :=0, @prevRank := NULL) r
ORDER BY score desc;
# 排名結(jié)果
+--------+-------+------+
| xuehao | score | rank |
+--------+-------+------+
| 1002 | 99 | 1 |
| 1003 | 96 | 2 |
| 1004 | 96 | 2 |
| 1008 | 94 | 3 |
| 1005 | 92 | 4 |
| 1006 | 90 | 5 |
| 1007 | 90 | 5 |
| 1001 | 89 | 6 |
+--------+-------+------+
3.并列排名，排名有間隔
另外一種排名方式是相同的值排名相同，相同值的下一個(gè)名次應(yīng)該是跳躍整數(shù)值，即排名有間隔。
# 查詢語句
SELECT xuehao, score, rank FROM
(SELECT xuehao, score,
@curRank := IF(@prevRank = score, @curRank, @incRank) AS rank,
@incRank := @incRank + 1,
@prevRank := score
FROM scores_tb, (
SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
) r
ORDER BY score desc) s;
# 排名結(jié)果
+--------+-------+------+
| xuehao | score | rank |
+--------+-------+------+
| 1002 | 99 | 1 |
| 1003 | 96 | 2 |
| 1004 | 96 | 2 |
| 1008 | 94 | 4 |
| 1005 | 92 | 5 |
| 1006 | 90 | 6 |
| 1007 | 90 | 6 |
| 1001 | 89 | 8 |
+--------+-------+------+
上面介紹了三種排名方式，實(shí)現(xiàn)起來還是比較復(fù)雜的。好在MySQL8.0增加了窗口函數(shù)，使用內(nèi)置函數(shù)可以輕松實(shí)現(xiàn)上述排名。
MySQL8.0 利用窗口函數(shù)實(shí)現(xiàn)排名
MySQL8.0中可以利用 ROW_NUMBER()，DENSE_RANK()，RANK() 三個(gè)窗口函數(shù)實(shí)現(xiàn)上述三種排名，需要注意的一點(diǎn)是as后的別名，千萬不要與前面的函數(shù)名重名，否則會(huì)報(bào)錯(cuò)，下面給出這三種函數(shù)實(shí)現(xiàn)排名的案例：
# 三條語句對(duì)于上面三種排名
select xuehao,score, ROW_NUMBER() OVER(order by score desc) as row_r from scores_tb;
select xuehao,score, DENSE_RANK() OVER(order by score desc) as dense_r from scores_tb;
select xuehao,score, RANK() over(order by score desc) as r from scores_tb;
# 一條語句也可以查詢出不同排名
SELECT xuehao,score,
ROW_NUMBER() OVER w AS 'row_r',
DENSE_RANK() OVER w AS 'dense_r',
RANK() OVER w AS 'r'
FROM `scores_tb`
WINDOW w AS (ORDER BY `score` desc);
# 排名結(jié)果
+--------+-------+-------+---------+---+
| xuehao | score | row_r | dense_r | r |
+--------+-------+-------+---------+---+
| 1002 | 99 | 1 | 1 | 1 |
| 1003 | 96 | 2 | 2 | 2 |
| 1004 | 96 | 3 | 2 | 2 |
| 1008 | 94 | 4 | 3 | 4 |
| 1005 | 92 | 5 | 4 | 5 |
| 1006 | 90 | 6 | 5 | 6 |
| 1007 | 90 | 7 | 5 | 6 |
| 1001 | 89 | 8 | 6 | 8 |
+--------+-------+-------+---------+---+
總結(jié)：
本文給出三種不同場景下實(shí)現(xiàn)統(tǒng)計(jì)排名的SQL，可以根據(jù)不同業(yè)務(wù)需求選取合適的排名方案。對(duì)比MySQL8.0，發(fā)現(xiàn)利用窗口函數(shù)可以更輕松實(shí)現(xiàn)排名，其實(shí)業(yè)務(wù)需求遠(yuǎn)遠(yuǎn)比我們舉的示例要復(fù)雜許多，用SQL實(shí)現(xiàn)此類業(yè)務(wù)需求還是需要慢慢積累的。
以上就是總結(jié)幾種MySQL中常見的排名問題的詳細(xì)內(nèi)容，更多關(guān)于MySQL 排名的資料請關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- MYSQL實(shí)現(xiàn)排名及查詢指定用戶排名功能（并列排名功能）實(shí)例代碼
- Mysql排序獲取排名的實(shí)例代碼
- MySQL頁面訪問統(tǒng)計(jì)及排名情況
- MySQL中給自定義的字段查詢結(jié)果添加排名的方法
- mysql分組取每組前幾條記錄(排名) 附group by與order by的研究
