【MySQL查询常见面试题】排名查询问题

前言

一道比较常见的MySQL查询面试题，求分数排名，假设表中有分数99 、99 、98 、97 、97 、96。
那排名应该是（1）99 、（1）99 、（2）98、 （3）97 、 （3）97 、 （4）96

1. 数据准备如下

mysql> desc t_rank;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
| score | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

mysql> insert into t_rank (name,score) values ('xiaoming',100),('xiaozhang',98),('xiaoli',98),('xiaowang',99),('xiaohong',97); 
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t_rank;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | xiaoming  |   100 |
|  2 | xiaozhang |    98 |
|  3 | xiaoli    |    98 |
|  4 | xiaowang  |    99 |
|  5 | xiaohong  |    97 |
+----+-----------+-------+
5 rows in set (0.04 sec)

2. 解题思路

一、先按分数降序排列一下

mysql> select name, score from t_rank order by score desc;
+-----------+-------+
| name      | score |
+-----------+-------+
| xiaoming  |   100 |
| xiaowang  |    99 |
| xiaozhang |    98 |
| xiaoli    |    98 |
| xiaohong  |    97 |
+-----------+-------+
5 rows in set (0.04 sec)

二、找出每个分数对应的排名

假设我们要查询大于等于100的有多少，那很简单

mysql> select count(distinct score) from t_rank where score >= 100;
+-----------------------+
| count(distinct score) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.04 sec)

查询大于等于99

mysql> select count(distinct score) from t_rank where score >= 99;
+-----------------------+
| count(distinct score) |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.04 sec)

查询大于等于98

mysql> select count(distinct score) from t_rank where score >= 98;
+-----------------------+
| count(distinct score) |
+-----------------------+
|                     3 |
+-----------------------+
1 row in set (0.05 sec)

查询大于等于97

mysql> select count(distinct score) from t_rank where score >= 97;
+-----------------------+
| count(distinct score) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.05 sec)

很明显，count出来的值刚好就每个分数对应的排名。

三、组合两部分逻辑

mysql> select score, (select count(distinct score) from t_rank a where a.score>=b.score) from t_rank b order by b.score desc;
+-------+---------------------------------------------------------------------+
| score | (select count(distinct score) from t_rank a where a.score>=b.score) |
+-------+---------------------------------------------------------------------+
|   100 |                                                                   1 |
|    99 |                                                                   2 |
|    98 |                                                                   3 |
|    98 |                                                                   3 |
|    97 |                                                                   4 |
+-------+---------------------------------------------------------------------+
5 rows in set (0.04 sec)

加上name字段

mysql> select distinct a.name,b.rank,b.score from t_rank a, (select score, (select count(distinct score) from t_rank a where a.score>=b.score) as 'rank' from t_rank b) b where a.score = b.score order by b.rank ;
+-----------+------+-------+
| name      | rank | score |
+-----------+------+-------+
| xiaoming  |    1 |   100 |
| xiaowang  |    2 |    99 |
| xiaoli    |    3 |    98 |
| xiaozhang |    3 |    98 |
| xiaohong  |    4 |    97 |
+-----------+------+-------+
5 rows in set (0.05 sec)