查询学生学科排名

表结构如下：

要求结果：

sql语句：

SELECT a.*, count(*) as rank 
FROM chengji a
JOIN chengji b ON a.xueke = b.xueke AND a.score <= b.score
GROUP BY a.xueke,a.name
ORDER BY a.xueke asc,a.score desc