MySQL 排名问题

分手后的思念是犯贱 2022-01-31 09:04 220阅读 0赞

MySQL要实现排名，可以借助变量。  
例如下面的score表，需要分别对数学和英语成绩排名，

mysql> select * from score;
    +----+--------+-------+---------+
    | id | xm     | math  | english |
    +----+--------+-------+---------+
    | 11 | 张三   | 78.00 |   23.00 |
    | 12 | 李四   | 87.00 |   45.00 |
    | 13 | 王五   | 54.00 |   67.00 |
    +----+--------+-------+---------+
    3 rows in set (0.00 sec)

可以利用下面的SQL实现

select a.id,a.xm,a.math,@math_rank := @math_rank+1 as math_rank
    from score a,(select @math_rank :=0) b
    order by a.math desc;
    +----+--------+-------+-----------+
    | id | xm     | math  | math_rank |
    +----+--------+-------+-----------+
    | 12 | 李四   | 87.00 |         1 |
    | 11 | 张三   | 78.00 |         2 |
    | 13 | 王五   | 54.00 |         3 |
    +----+--------+-------+-----------+
    3 rows in set (0.00 sec)
    
    select a.id,a.xm,a.english,@english_rank := @english_rank+1 as english_rank
    from score a,(select @english_rank :=0) b
    order by a.english desc;
    +----+--------+---------+--------------+
    | id | xm     | english | english_rank |
    +----+--------+---------+--------------+
    | 13 | 王五   |   67.00 |            1 |
    | 12 | 李四   |   45.00 |            2 |
    | 11 | 张三   |   23.00 |            3 |
    +----+--------+---------+--------------+
    3 rows in set (0.00 sec)

如果要在一个结果集里同时对数学和英语成绩进行排名呢？可以如下实现

select c.id,c.xm,c.math,d.english,c.math_rank,d.english_rank from 
    (select a.id as id,a.xm as xm,a.math as math,@math_rank := @math_rank+1 as math_rank
    from score a,(select @math_rank :=0) b
    order by a.math desc) c
    join 
    (select a.id as id,a.xm,a.english as english,@english_rank := @english_rank+1 as english_rank
    from score a,(select @english_rank :=0) b
    order by a.english desc) d
    on c.id=d.id
    +----+--------+-------+---------+-----------+--------------+
    | id | xm     | math  | english | math_rank | english_rank |
    +----+--------+-------+---------+-----------+--------------+
    | 12 | 李四   | 87.00 |   45.00 |         1 |            2 |
    | 11 | 张三   | 78.00 |   23.00 |         2 |            3 |
    | 13 | 王五   | 54.00 |   67.00 |         3 |            1 |
    +----+--------+-------+---------+-----------+--------------+
    3 rows in set (0.00 sec)