本题目要求编写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
);
表样例
思路:
1. 首先,我们使用两个子查询(子查询b1和子查询b2)来获取学生的学号、姓名、班级名称和分数。在子查询b1中,我们通过连接tb_score(成绩表)、tb_class(班级表)、tb_student_class(学生班级关联表)和tb_student(学生表),获取到学生的学号、姓名、班级名称和分数。在子查询b2中,我们通过连接tb_score、tb_class和tb_student_class,获取到每个班级中的最低分数。
2. 然后,在主查询中,我们使用WHERE子句来筛选出满足条件的学生。我们通过比较班级名称和分数,找出每个班级中成绩最低的学生。
3. 最后,我们在主查询中选择子查询b1中的学号、姓名、班级名称和分数作为结果。
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