mysql之多表联动查询

要求：查询各班各科分数最高的学生学号、姓名、科目编号、班级编号、分数

CREATE DATABASE IF NOT EXISTS stu;
USE stu;
CREATE TABLE IF NOT EXISTS class 
(classID INT PRIMARY KEY AUTO_INCREMENT, className VARCHAR(20) ); 
CREATE TABLE IF NOT EXISTS student(
stuID INT PRIMARY KEY AUTO_INCREMENT,
classID INT,
stuName VARCHAR(40));
CREATE TABLE IF NOT EXISTS score(
courseID INT,
stuID INT,
sourse VARCHAR(40),
socre INT);
INSERT INTO class VALUES
(NULL,'一班'),(NULL,'二班'),(NULL,'三班');
INSERT INTO student VALUES
(1001,1,'张三'),
(NULL,1,'李丽'),
(NULL,1,'钱封'),
(NULL,2,'杨国'),
(NULL,2,'小样'),
(NULL,2,'区天'),
(NULL,3,'李三宅'),
(NULL,3,'黄武'),
(NULL,3,'赵六');
INSERT INTO score VALUES 
(2,1001,'数学',73),
(3,1001,'英语',79),
(1,1001,'语文',81),
(3,1002,'英语',87),
(3,1002,'英语',87),
(2,1002,'数学',83),
(1,1002,'语文',79),
(1,1003,'语文',65),
(3,1003,'英语',65),
(2,1003,'数学',97),
(1,1004,'语文',78),
(3,1004,'英语',78),
(2,1004,'数学',86),
(1,1005,'语文',67),
(3,1005,'英语',88),
(2,1005,'数学',89),
(2,1006,'数学',90),
(3,1006,'英语',92),
(1,1006,'语文',98),
(1,1007,'语文',85),
(2,1007,'数学',78),
(3,1007,'英语',72),
(1,1008,'语文',78),
(3,1008,'英语',77),
(2,1008,'数学',85),
(3,1009,'英语',94),
(2,1009,'数学',91),
(1,1009,'语文',68);

解题思路:

根据题目要求，需要优先把所有人的成绩查询出来。想法是把三张表融合成一张
表。

SELECT a.className,b.`sourse`,b.`score`,c.`stuName`,c.`stuID` FROM 
class AS a, score b ,student c WHERE (c.`stuID`=b.`stuID` AND a.classID =c.`classID` ) ORDER BY b.`score` DESC

上述代码执行后出现的结果是：

代码执行后会出现一张新的表，但此表是临时的结果，可以把它当做一张临表来使用，此表中包含了所有的题目中的要求，所以，查询此表中的记录即可。

SELECT t.stuID 学号,t.stuName 姓名,t.sourse 科目,t.className 班级, MAX(t.score) 分数 FROM (SELECT
a.className,b.`sourse`,b.`score`,c.`stuName`,c.`stuID` 
FROM class AS a, score b ,student c WHERE (c.`stuID`=b.`stuID` AND a.classID =c.`classID` ) ORDER BY b.`score` DESC) t GROUP BY t.className,t.sourse;

查询结果