hackerrank - MySql rank如何实现

MySQL:

1. 使用user-defined variable来给table进行普通排序

比如Player table如下：

pid	name	age
1	Samual	25
2	Vino	20
3	John	20
4	Andy	22
5	Brian	21
6	Dew	24
7	Kris	25
8	William	26
9	George	23
10	Peter	19
11	Tom	20
12	Andre	20

 

Select pid, name, age, @curRank := @curRank +1 as rank

From Player p, (select @curRank := 0) c

Order by age asc;

得到排序后的table如下：

| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|  10 |   Peter |  19 |    1 |
|  12 |   Andre |  20 |    2 |
|   2 |    Vino |  20 |    3 |
|   3 |    John |  20 |    4 |
|  11 |     Tom |  20 |    5 |
|   5 |   Brian |  21 |    6 |
|   4 |    Andy |  22 |    7 |
|   9 |  George |  23 |    8 |
|   6 |     Dew |  24 |    9 |
|   7 |    Kris |  25 |   10 |
|   1 |  Samual |  25 |   11 |
|   8 | William |  26 |   12 |

要在mysql中声明一个变量，你必须在变量名之前使用@符号。FROM子句中的(@curRank := 0)部分允许我们进行变量初始化，而不需要单独的SET命令。当然，也可以使用SET，但它会处理两个查询：

SET @curRank := 0;
SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players
ORDER BY age

这个排序相当于MSSQL和Orcle中的row_number()排序

2. 为并列的数据实现相同的排名

为并列数据的行赋予相同的排名，则意味着那些在排名比较列中具有相同值的行应在MySQL中计算排名时保持相同的排名，为此我们使用的一个额外变量：

SELECT pid, name, age, 
CASE 
WHEN @prevRank = age THEN @curRank 
WHEN @prevRank := age THEN @curRank := @curRank + 1
END AS rank
FROM players p, 
(SELECT @curRank :=0, @prevRank := NULL) r
ORDER BY age

| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|  10 |   Peter |  19 |    1 |
|  12 |   Andre |  20 |    2 |
|   2 |    Vino |  20 |    2 |
|   3 |    John |  20 |    2 |
|  11 |     Tom |  20 |    2 |
|   5 |   Brian |  21 |    3 |
|   4 |    Andy |  22 |    4 |
|   9 |  George |  23 |    5 |
|   6 |     Dew |  24 |    6 |
|   7 |    Kris |  25 |    7 |
|   1 |  Samual |  25 |    7 |
|   8 | William |  26 |    8 |

此排序方式相当于MSSQL和Orcle中的dense_rank()排序

3. 实现高级并列函数排名

当使用RANK()函数时，如果两个或以上的行排名并列，则相同的行都会有相同的排名，但是下一个排名会空出之前排名所占的位子。

SELECT pid, name, age, rank FROM
(SELECT pid, name, age,
@curRank := IF(@prevRank = age, @curRank, @incRank) AS rank, 
@incRank := @incRank + 1, 
@prevRank := age
FROM players p, (
SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
) r 
ORDER BY age) s

这是一个查询中的子查询。我们使用三个变量(@incRank，@prevRank，@curRank)来计算关系的情况下，在查询结果中我们已经补全了因为并列而导致的排名空位。我们已经封闭子查询到查询。

| PID |    NAME | AGE | RANK |
|-----|---------|-----|------|
|  10 |   Peter |  19 |    1 |
|  12 |   Andre |  20 |    2 |
|   2 |    Vino |  20 |    2 |
|   3 |    John |  20 |    2 |
|  11 |     Tom |  20 |    2 |
|   5 |   Brian |  21 |    6 |
|   4 |    Andy |  22 |    7 |
|   9 |  George |  23 |    8 |
|   6 |     Dew |  24 |    9 |
|   7 |    Kris |  25 |   10 |
|   1 |  Samual |  25 |   10 |
|   8 | William |  26 |   12 |

reference：https://www.jianshu.com/p/bb1b72a1623e

https://fellowtuts.com/mysql/query-to-obtain-rank-function-in-mysql/

---

---

• row_number()排序：row_number() over( [partition by ...] order by clause)
• rank()排序：RANK() OVER ([PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ...)
• dense_rank()排序：

2 3 4

DENSE_RANK() OVER (     [PARTITION BY partition_expression, ... ]     ORDER BY sort_expression [ASC | DESC], ... )