SQL查询学生表中总成绩最高的学生的姓名和总成绩

今天面试遇到的一道SQL题，憋了半天没答出来。回家第一件事就是Google答案，但找到的一些文章都不是完全符合题目要求，所以自己建了个测试表来折腾了。

表结构是这样的：

 

 要求是查出各科总成绩最高的学生姓名和总成绩。（注：第一名可能重分）

思路大概是根据姓名聚合查询出name和 sum(score)，并根据score排序，再取出score最大的数据。这里有一个坑点，就是成绩最高的人可能不止一个，所以max函数和 limit 1 不能实现需求，也是因为这个，才真正了解了MySQL的变量和判断语句。下面是解题思路：

第一步：聚合查询name和sum(score)，并排序

SELECT `name` ,SUM(score) AS sum_score FROM grade GROUP BY `name` ORDER BY `sum_score` DESC

得到下面的数据

 

 如果不考虑重分的情况，到这一步直接返回第一条数据就完成任务了。

这时候就要用到sql的判断了，但首先我们要有一个判断的条件。在这里我是选择在刚得到的数据基础上，新增一列rank，记录排名。具体如下：

第二步：增加排名列

SET @rank = 0;
SET @last_score = 0;

SELECT *, 
     CASE 
         WHEN @last_score = u.sum_score
             THEN @rank 
         WHEN @last_score := u.sum_score 
             THEN @rank := @rank + 1    
        END AS rank   
FROM (SELECT `name` ,SUM(score) AS sum_score FROM grade GROUP BY `name` ORDER BY `sum_score` DESC) u;

返回数据如下：

这一步用到了mysql中的变量和判断语句，

第三步：从查询出的数据中再查出rank等于1的数据

SET @rank = 0;
SET @last_score = 0;

SELECT `name`,`sum_score` FROM (
    SELECT *, 
         CASE 
             WHEN @last_score = u.sum_score
                 THEN @rank 
             WHEN @last_score := u.sum_score 
                 THEN @rank := @rank + 1    
            END AS rank   
    FROM (SELECT `name` ,SUM(score) AS sum_score FROM grade GROUP BY `name` ORDER BY `sum_score` DESC) u
) t where `rank` = 1;

结果如下：

符合我们题目的要求