mysql实现排行榜_mysql之排名实现
前言： mysql没有实现类似排名(rank)功能的函数。但是我们可以通过基数的查询加上其他函数可是实现类似的功能。
题目： 编写一 个 SQL 查询来实现分数排名。
一： 首先我们创建一 张并插入一些数据如下， 用于方便后面排名的演⽰。
CREATE TABLE`players ` (
`pid`int(2) NOT NULLAUTO_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);
二： 简单排名， 相同的年纪随机分配排名次序
SELECT pid, name, age, @curRank := @curRank + 1 ASrankFROMplayers p, (SELECT @curRank := 0) qORDER BY age
解题分析 ：声明一 个session级别的变量(注意在sql中声明变量需要在变量名前加@)。
步骤一 ：在子查询中声明一 个变量， 并初始化为0。在select函数每记录一行数据变量加1(在select函数后‘： = ’为赋值操作)。
结果：
| 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 |
备注1： 我们通过子查询把声明变量和select函数通过一条语句完成(利用子查询)。我们也可以分离声明变量和查询分开如备注2
备注2：分离声明变量和查询分， 通过两条sql。
sql1SET @curRank := 0;
sql2SELECT pid, name, age, @curRank := @curRank + 1 ASrankFROMplayersORDER BY age
三：相同的分数需有相同的排名名次， 排名无间隙
SELECTpid, name, age,CASE
WHEN @prevRank = age THEN @curRank
WHEN @prevRank := age THEN @curRank := @curRank + 1
END ASrankFROMplayers p,
(SELECT @curRank :=0, @prevRank := NULL) rORDER 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 |
解题分析 ：通过格外一 个变量， 记录上一条记录的年纪。通过比较当前年龄和该变量是否相同来判断排名是否加一。
备注3：分析sql
CASE
WHEN @prevRank = age THEN @curRank
WHEN @prevRank := age THEN @curRank := @curRank + 1
END
第一 个 WHEN @prevRank = age THEN @curRank 。 是判断语句， 用于判断上一条数据的年纪和当前数据的年纪是否相等， 如果为 true， 返回当前排名。如果为false， 继续执行下一条WHEN
第二 个 WHEN @prevRank := age THEN @curRank := @curRank + 1 。是赋值， 即把当前年龄赋值@prevRank(赋值操作总是返回 为true)， 并排名加一。
四：相同的分数需有相同的排名名次， 排名有间隙
SELECT pid, name, age, rank FROM(SELECTpid, name, age,@curRank := IF(@prevRank = age, @curRank, @incRank) ASrank,@incRank := @incRank + 1,@prevRank :=ageFROMplayers p, (SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) rORDER BY age) s
结果
| 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 |
解题分析 ：在上一 个sql的基础上再另加一 个变量， 用于记录select读取的行数. (即标题2中的排名)。并用三目运算符来判断使用哪个排名。
备注： mysql中IF(expr1,expr2,expr3)函数是我们熟悉的三目运算函数。即If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.
