帮我想想查询sql怎么写,数据库是mysql

Question

需求是取每个人的前两个最高分(例如A就是80和70)之差, 有的人可能只有一条记录(例如C), 只有一条记录的差设为0
+----+------+------+
| id | name | score|
+----+------+------+
| 1 | A | 50 |
| 2 | A | 80 |
| 3 | A | 70 |
| 4 | B | 80 |
| 5 | B | 85 |
| 6 | C | 90 |
+----+------+------+

我想按name分组, score降序, 给每组内的记录标序号(如下表), 这样获得前两个最高分, 可是不知道怎么标号? 除了标号还有别的思路没

+------+------+------+
| name | score|序号 |
+------+------+------+
| A | 80 | 1 |
| A | 70 | 2 |
| A | 50 | 3 |
| B | 85 | 1 |
| B | 80 | 2 |
| C | 90 | 1 |
+------+------+------+

Answer 1

select a.name,if(c.name is null,0,(a.score-c.score)) from (select max(score) score,name from chengji group by name) a left join (select max(b.score) score,b.name from (select max(score) score,name from chengji group by name ) a right join chengji b on b.name=a.name and b.score =a.score where a.name is null group by b.name ) c on c.name =b.name