MySQL函数&关键字（九）排名函数 非rank窗口函数

MySQL 官方手册 8.0 Reference Manual - Window Function
本文转自：Query to obtain rank function in MySQL

MySQL8.0之后开始支持窗口函数，其中排序函数有：

ROW_NUMBER()：顺序排序——1、2、3
RANK()：并列排序，跳过重复序号——1、1、3
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2

MySQL中8.0之前没有Rank排名函数，每当需要根据 MySQL 中的某些功能/标准从表中查询排名时。但是有一些简单的查询可用于执行相同的操作。

在这里，我将通过示例描述执行排名的查询：

首先，我们正在创建players我们想要获得排名的表。根据不同的要求，您将熟悉许多查询。

CREATE TABLE `players` (
  `pid` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(2) NOT NULL,
  PRIMARY KEY (`pid`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO `players` (`pid`, `name`, `age`) VALUES
(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);

查询获取 MySQL 中的排名函数

这里我们希望获得一个按照玩家年龄升序排列的排名列。也就是MySQL8.0的窗口函数ROW_NUMBER()；

所以我们的查询将是：

SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players p, (
SELECT @curRank := 0
) q
ORDER BY age

| 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 中声明变量，您必须在变量名前使用“@”符号。(@curRank := 0)FROM 子句中的部分允许我们在不需要单独的 SET 命令的情况下进行变量初始化。您也可以使用SET ，但它会处理两个查询：

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

查询以降序计算排名

获得按年龄降序排列的排名，然后修改查询的 ORDER BY子句DESC和列名：

SELECT pid, name, age, @curRank := @curRank + 1 AS rank
FROM players p, (
SELECT @curRank := 0
) q
ORDER BY age DESC, name

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

行连接时在 MySQL 中的排名

现在，如果我们希望为有关系的行分配相同的等级意味着age在 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 |

如图所示，当同一分区中的一个等级有两行或更多行时，每个绑定的行都会收到相同的等级。玩家 Andre、Vino、John 和 Tom 的年龄相同，因此他们排在第 2 位。年龄次高的玩家 (Brian) 排在第 3 位。此查询等效 于 MSSQL 和 ORACLE 中的**DENSE_RANK()**函数。也就是MySQL8.0的窗口函数DENSE_RANK()；

RANK() 在 MySQL 中等价

在使用 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) 我们将子查询包含在查询中。它是MSSQL 和 ORACLE 中可用的**RANK()**函数。也就是MySQL8.0的窗口函数RANK()；

| 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 |

在这里我们可以看到玩家 Andre、Vino、John 和 Tom 的年龄相同，所以他们排在第 2 位。年龄次高的玩家（Brian）排在第 6 位而不是第 3 位，因为有四行排位在位置 2。