今天⾯试遇到的⼀道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;
结果如下：
符合我们题⽬的要求
发布评论