10-1 sql-sample(超详细逐步解析)

本题目要求编写SQL语句，检索出每个班级中分数最低的同学id，姓名，分数，班级名称a111

表结构:

create table tb_student (
    id int not null primary key,
    name varchar(32)
);
create table tb_score (
    stu_id int,
    score int
);
create table tb_class (
    id int not null,
    name varchar(32)
);
create table tb_student_class
(
    id       int null,
    class_id int null,
    stu_id   int null
);

表样例

tb_student表：

id	name
30	ddd
49	ccc
51	aaa
52	bbb

tb_score表：

stu_id	score
30	99
49	79
51	80
52	59

tb_class表：

id	name
1	class-1
2	class-2
3	class-1
4	class-2

tb_student_class表

id	stu_id	class_id
1	30	1
2	49	2
3	51	1
4	52	2

输出样例:

stu_id	stu_name	class_name	score
51	aaa	class-1	80
52	bbb	class-2	59

第一步 ： 三表连接找到各班的所有成绩

 

第二步 ： 改进第一步语句找到各个班成绩最低的分数

 第三部 ： 在第一步的基础上创建包括学生姓名在内的信息全表

 最后一步 ： 寻找图3与图二的交集

 

 源代码在这哩~

select b1.stu_id,b1.stu_name,b1.class_name,b1.score
from
(
select a3.stu_id,a4.name as stu_name,a2.name as class_name,score
from tb_score as a1,tb_class as a2,tb_student_class as a3,tb_student as a4
where a1.stu_id = a3.stu_id and a3.class_id = a2.id and a4.id = a3.stu_id
) as b1,
(
select name,min(score) as mins
from tb_score as a1,tb_class as a2,tb_student_class as a3
where a1.stu_id = a3.stu_id and a3.class_id = a2.id
group by name
) as b2
where b1.class_name = b2.name and b1.score = b2.mins