[数据库] SQL 经典面试题 - 数据库查询 - 子查询应用一

面试题

柠檬班第30期学生要毕业了，他们的成绩存放在下表中，现在需要查询每个班的最高分同学，并且显示该同学的名字！

建表和初始化数据

-- 1:创建表
CREATE TABLE tb_lemon_grade (
 id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 student_name VARCHAR(20) DEFAULT NULL,
 Linux int(3) DEFAULT 0,
 MySQL int(3) DEFAULT 0,
 Java  int(3) DEFAULT 0,
 class_name VARCHAR(20) DEFAULT NULL);

-- 2:初始化数据
INSERT INTO tb_lemon_grade (student_name, Linux, MySQL,Java,class_name) VALUES
("刘一", 80, 60,70,'18期'),
("陈二", 70, 90,80,'18期'),
("张三", 90, 80,70,'18期'),
("李四", 60, 80,70,'19期'),
("王五", 60, 70,90,'19期'),
("赵六", 70, 80,60,'19期'),
("孙七", 100,70,80,'20期'),
("周八", 70, 80,90,'20期'),
("吴九", 80, 70,90,'21期'),
("郑十", 90, 70,60,'21期');

分析题目

数据表中每行记录保存的是每个学生的姓名、各科成绩和班级，现在需求是求出每个班的最高分，所以肯定需要以班级去进行分组(GROUP BY)，那最高分的筛选呢，使用聚合函数MAX()吗？
我们说MAX()是求纵向的最大值，但是现在需要求最高分，意思是同一个同学的各科目间也得进行比较，上图红色加粗的成绩就是每个班的最高分，这是通过肉眼查看心算比较得到的结果，我们的心算过程非常简单：找到这个班的所有成绩，再比较各个成绩值，找出最大的分数（重复的也算，相当于并列第一）。
因此，还需要横向的比较！这里介绍一个函数：GREATEST()，看官方的帮助文档，下面的语法说明和例子告诉我们，GREATEST()函数可以返回多个参数间的最大值：

实现

现在开始写SQL

第一步：先找出每个班的最高分

先分组、再进行MAX()纵向比较得到每科的最大分、再通过GREATEST()横向比较，进而得到该班级的最高分！

SELECT class_name, GREATEST( MAX(Linux), MAX(MySQL), MAX(Java))
FROM tb_lemon_grade GROUP BY class_name;

第二步：怎么求出每个班的最高分同学？

第一步我们已经得到了每个班的最高分，注意这个最高分并不能确定是某个同学的，因为可能存在多个同学都是最高分（比如班级18期的陈二和张三，都有一科为最高分90分）
通过自然思维筛选出最高分的过程：我已知了该班级的最高分为90分，然后我会拿到这个分数会和这个班中的每个同学的各科分数进行笔记，如果某同学有一门课程的分数也是90分，那就是我们要找的同学！
是不是很好理解？所以，我们先求出每个同学的最高分

第三步：求出每个同学的最高分（横向得到该同学各科的最高分）

SELECT *, GREATEST(Linux, MySQL, Java) maxScore 
FROM tb_lemon_grade

第四步：两个子查询的结果集关联查询！

通过两个子查询，一个是每个班的最高分学习，一个是每个同学的信息和最高分！

SELECT t2.class_name, t2.student_name, t1.maxScore 
FROM 
( 
	SELECT class_name, GREATEST( MAX(Linux), MAX(MySQL), MAX(Java)) maxScore
	FROM tb_lemon_grade 
	GROUP BY class_name 
) t1,
( SELECT *, GREATEST(Linux, MySQL, Java) maxScore FROM tb_lemon_grade) t2 
WHERE t1.class_name = t2.class_name 
AND t1.maxScore = t2.maxScore;

题目看上去很简单，写起来并不容易，重点在解题思路！！

测试派 Give testers a home.