MySQL常见排名面试题 - 变量@的使用

之前见过一条SQL，解决了排名的问题，当时就觉得很高大上，这业务要是我来实现估计又是一顿for循环了。今天又遇到类似的场景，记录一下吧，以后要养成做笔记的习惯。

场景如下：

1. 考试后，统计各科成绩的排名
   效果如下：
   
2. 考试后，统计每个学生各科成绩的分数，按降序排名
   效果如下：
   

以下是本例子中用的SQL：

CREATE TABLE `student_score` (
  `name` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
  `class` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '科目',
  `score` int(11) DEFAULT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


INSERT INTO `student_score` VALUES ('张三', '语文', '80');
INSERT INTO `student_score` VALUES ('张三', '数学', '90');
INSERT INTO `student_score` VALUES ('张三', '英语', '99');
INSERT INTO `student_score` VALUES ('李四', '语文', '70');
INSERT INTO `student_score` VALUES ('李四', '数学', '60');
INSERT INTO `student_score` VALUES ('李四', '英语', '80');
INSERT INTO `student_score` VALUES ('王五', '语文', '80');
INSERT INTO `student_score` VALUES ('王五', '数学', '80');
INSERT INTO `student_score` VALUES ('王五', '英语', '80');
INSERT INTO `student_score` VALUES ('赵六', '语文', '50');
INSERT INTO `student_score` VALUES ('赵六', '数学', '30');
INSERT INTO `student_score` VALUES ('赵六', '英语', '20');
INSERT INTO `student_score` VALUES ('冯七', '语文', '76');
INSERT INTO `student_score` VALUES ('冯七', '数学', '87');
INSERT INTO `student_score` VALUES ('姚八', '语文', '31');
INSERT INTO `student_score` VALUES ('姚八', '英语', '34');
INSERT INTO `student_score` VALUES ('周九', '数学', '89');
INSERT INTO `student_score` VALUES ('周九', '英语', '98');

第一个场景SQL如下：

-- 统计各科成绩的排名
SELECT s.`name` '姓名',s.class '科目',s.score '成绩',
CASE WHEN s.class = @kemu then @rank:= @rank+1 else @rank:=1 end as '排名',
@kemu := s.class
from student_score s,(SELECT @kemu:='',@rank:=0)t 
ORDER BY s.class,s.score desc;

说明：

1. @kemu 和 @rank 作为一个用户变量参与SQL的查询，关于用户变量，官方解释：
   翻译后：
2. 变量赋值用 := ，该SQL中初始化变量 @kemu:=’’,@rank:=0，变量如果不赋值，则默认为null。

第二个场景SQL如下：

-- 统计每个学生各科成绩的分数，按降序排名
SELECT s.`name` '姓名',s.class '科目',s.score '成绩' ,
CASE WHEN s.`name` = @name then @rank:= @rank+1 else @rank:=1 end as '排名',
@name := s.`name` 
from student_score s,(SELECT @name:='',@rank:=0)t 
ORDER BY s.`name`,s.score DESC;

以上两个SQL查出来会多一列，可以在外层再套一层select。这两个SQL语句中的 case when 也可以用 if 来替换：类似 ：

if(s.name = @name,@rank:= @rank + 1,@rank:=1) as rank

不知道这样子是不是最优SQL，大家如果有更好的SQL，还请赐教，谢谢了