mysql题目实战2

梁霖编程工具库 2023-02-26

267

题目描述：查询评价成绩大于等于60分的学生编号、学生姓名和学生成绩

select stu.sid,stu.sname,sc1.cid,sc1.score,tmp.avg_score
from student stu
inner join(
    -- 因为是按sid作为分组，而这个分组只有唯一的一个平均分，因此可以sid和avg(score)同时出现
  select sid,avg(score) as avg_score
  from sc
  group by sid
  ) as tmp
on stu.sid = tmp.sid
inner join sc sc1 
on sc1.sid = stu.sid
where sc1.score >= tmp.avg_score;

使用group by的注意事项

注意group by sid 以后相同的sid只会出现一次，虽然一个sid有多个score，但也只会出现第一个socre

select sid,score,avg(score) as avg_score from sc group by sid

题目描述：查询sc表存在成绩的学生信息

注意可以通过group by去重

select student.*
from sc
inner join student
on sc.sid = student.sid
-- group by 可以进行去重复
group by sc.sid;

select student.*
from (
-- 先把有成绩的学生信息通过group by进行去重，得到的sid都是有成绩的学生id
  select sid from sc group by sid
) sc_tmp 
left join Student
on sc_tmp.sid = student.sid

题目描述：查询所有同学的学生编号，学生姓名，选课总数，所有课程的总成绩（没有则显示null）

思路1:先找出有成绩的学生，找出没有成绩的学生，通过union进行联合

-- 已选课程的同学
select s.sid,s.sname,COUNT(s.sid) as total_count,SUM(c.score) as total_score
from student s
inner join sc c
on s.sid = c.sid
group by s.sid
union
-- 没选课程的同学
select s.sid,s.sname,0 as total_count, null as total_score
from student s
where s.sid not in( select distinct sc.sid from sc)

思路2：
对sc表先按sid进行聚合处理，处理完以后用student和聚合处理后的sc表进行左连接left join

select stu.sid,stu.sname,tmp.total_count,tmp.total_score
from Student stu
left join(
  -- 对sc表先按sid进行聚合，第二步用student和聚合的结果进行左连接
  select sid, count(sid) as total_count,sum(score) as total_score
  from sc
  group by sc.sid) tmp
on stu.sid = tmp.sid;

题目描述：查询有成绩的学生信息

思路：成绩表sc表作为主表来left join student表，注意通过group by去重

select s.*
from sc c
inner join student s
on c.sid = s.sid
-- 不增加group by会有很多重复的结果
group by s.sid;

思路：先对sc表进行过滤去重，然后驱虫后的结果left join 学生表

select s.*
from(
  select sid
  from sc
  group by sid) tmp
left join student s
on tmp.sid = s.sid;

题目描述：统计李姓老师的数量

select count(*) as 李姓老师数量
from Teacher t
where t.Tname like '李%'

题目描述：查询学过张三老师授课的学生信息

-- 找到学生信息
select student.*
from student
where student.sid in(
  -- 找到选修了张三老师教授课的学生编号
  select sc.sid
  from sc
  where sc.cid in(
    -- 找到张三老师教的课程编号
    select course.cid
    from Course
    where course.tid in(
      -- 找到张三老师的教师编号
      select tid
      from Teacher
      where tname = '张三'
    )
  )
);

题目描述：查询没有学全所有课程的学生信息

第1步：查询一共有多少门课程
第2步：
查询学生课程关系表按学生分组以后每个学生学了几门课程
第3步：
过滤出第2步中不等于第1步中课程数的结果
第4步：最后查询学生信息表

-- 选了课程但没有选全的
select student.*
from student
inner join(
  select sc.sid,count(sc.sid) as cnt 
  from sc
  group by sc.sid
  -- 这里要用having来过滤而不能用where过滤
  having cnt != (select count(*) as cnt from course)
) tmp 
on student.sid = tmp.sid
union
-- 一门课程都没选的
select student.*
from student
where student.sid not in(
  select sid from sc group by sid
)

题目描述：查询至少有一门课与01同学所学相同的学生信息

select student.*
from student
where student.sid in(
  select sc.sid
  from sc
  where sc.cid in(
    select distinct sc.cid
    from sc
    where sc.sid = '01'
  ) and sc.sid != '01'
)

文章转载自梁霖编程工具库，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

mysql题目实战2

评论