-- 1.查询01课程成绩比02课程成绩高的学生的学号、姓名以及两门课分别的成绩
select student.s_id,student.s_name,t1.01_score,t2.02_score from student
left join ( select s.s_id,sc.c_id,sc.s_score 01_score from student s
left join score sc on s.s_id=sc.s_id
where sc.c_id = 01
) as t1
on student.s_id = t1.s_id
left join ( select s.s_id,sc.c_id,sc.s_score 02_score from student s
left join score sc on s.s_id=sc.s_id
where sc.c_id = 02
) as t2
on student.s_id = t2.s_id
where t1.01_score > t2.02_score
-- 2.查询所有学生的学号、姓名、选课门数以及平均成绩
select s.*
,ifnull((select avg(s_score) from score where score.s_id = s.s_id),0) course_avg_score
,(select count(c_id) from score where score.s_id = s.s_id) course_count
from student s
-- 使用group by 的写法
select s.s_id,s.s_name,ifnull(avg(c.s_score),0),count(c.c_id) from student s
left join score c on s.s_id=c.s_id
group by s.s_id
-- 3.查询没有学过张三老师所教课程的学生的学号和姓名
select * from student s where s.s_id not in (
select score.s_id from score
where score.c_id = (select c.c_id from teacher t
left join course c on t.t_id = c.t_id
where t.t_name = '张三'
)
)
-- 4.查询所有选修的都不及格课程成绩的学生的学号和姓名
select s.s_id,s.s_name from student s
where (select MAX(score.s_score) from score where s.s_id=score.s_id) < 60
-- 5.查询每个学生的学号、姓名以及每门课的成绩(每个学生对应查询结果中的一条记录,多门课程成绩显示在同一行)
select s.s_id,s.s_name
,ifnull((select score.s_score from score where score.s_id = s.s_id AND score.c_id = '01'),0) '数学'
,ifnull((select score.s_score from score where score.s_id = s.s_id AND score.c_id = '02'),0) '语文'
,ifnull((select score.s_score from score where score.s_id = s.s_id AND score.c_id = '03'),0) '英语'
from student s
MySQL子查询和连接查询例题
最新推荐文章于 2023-09-08 19:23:39 发布