Hot For Coding

SQL计算排名

通常我们读取排行榜的时候很容易,直接按照字段倒叙查询出来就好,但如果要取得某条记录的排名,需要动下脑筋,因为MySQL本身不能算出这样的需求,需要变动一下即可算出记录在表中的名次。

比如有一个表:

mysql> desc c;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(10) | NO   | PRI | NULL    | auto_increment |
| fen   | int(10) | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
 
mysql> select * from c;
+----+------+
| id | fen  |
+----+------+
|  1 |  300 |
|  2 |  200 |
|  3 |  500 |
|  4 |  600 |
|  5 |  800 |
|  6 | 1000 |
|  7 |  200 |
|  8 |  400 |
|  9 |  700 |
| 10 |  600 |
| 11 |  100 |
+----+------+
11 rows in set (0.00 sec)
 
mysql> 

如果要以fen字段算出每条记录的排名,很容易,直接desc即可

mysql> select * from c order by fen desc;
+----+------+
| id | fen  |
+----+------+
|  6 | 1000 |
|  5 |  800 |
|  9 |  700 |
| 10 |  600 |
|  4 |  600 |
|  3 |  500 |
|  8 |  400 |
|  1 |  300 |
|  7 |  200 |
|  2 |  200 |
| 11 |  100 |
+----+------+
11 rows in set (0.02 sec)

但如果我要求得id=3的记录在表中的名次,就不能这么写了,可以这么算。

mysql> select count(*)+1 from c where fen>500;
+------------+
| count(*)+1 |
+------------+
|          6 |
+------------+
1 row in set (0.01 sec)

因为id=3的fen值等于500,所以大于500的都排在它前面。如果两个分数一样,就按照id从小到大排序,比如算出id=10的排名

我们先分两步,第一步先算出大于600的排名

mysql> select count(*)+1 from c where fen>600;
+------------+
| count(*)+1 |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

算出等于600在id中的排名

mysql> select count(*) from c where fen=600 and id<10;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

两个数的和就是id=10在整张表中的排名4+1=5第五位,那么id=4的fen值在表中的排名就是第四位。我们来看下整表排序效果

mysql> select * from c order by fen desc,id asc;
+----+------+
| id | fen  |
+----+------+
|  6 | 1000 |
|  5 |  800 |
|  9 |  700 |
|  4 |  600 |
| 10 |  600 |
|  3 |  500 |
|  8 |  400 |
|  1 |  300 |
|  2 |  200 |
|  7 |  200 |
| 11 |  100 |
+----+------+
11 rows in set (0.00 sec)

id=4与id=10分别排在第四、第五位。与前面计算结果相同。

分享

TITLE: SQL计算排名

LINK: https://www.qttc.net/281-sql-calculate-rank.html

NOTE: 原创内容,转载请注明出自琼台博客