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.