MySQL 排名问题

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)