侧边栏壁纸
博主头像
小鱼吃猫博客博主等级

你所热爱的,便是你的生活。

  • 累计撰写 114 篇文章
  • 累计创建 47 个标签
  • 累计收到 9 条评论

目 录CONTENT

文章目录

查询至少有一门课与学号为"01"的同学所学相同的同学的信息

小鱼吃猫
2023-11-04 / 0 评论 / 2 点赞 / 33 阅读 / 2494 字

背景

MySQL经典练习题及答案,常用SQL语句练习50题

查询至少有一门课与学号为"01"的同学所学相同的同学的信息

先查询出01同学学过的课程id,然后判断下学过这几个id课程的同学

select distinct stu.*
from student stu
         left join score sc
                   on stu.s_id = sc.s_id
where sc.c_id in (select c_id from score where s_id = '01');

查询和"01"号的同学学习的课程完全相同的其他同学的信息

  • Step1. 查询出01同学学过的课程
select c_id from score where s_id = '01'
  • Step2. 假设所有同学都学过这几个课程,做一个连接,然后判断是否真的学过对应的课程,这里应该用full join,但是MySQL没有这个,所以用unionleft joinright join结合。
select t.*,sc.c_id cid2
from (select *
      from student
         , (select c_id from score where s_id = '01') t) t
         left join score sc
                   on t.s_id = sc.s_id
                       and t.c_id = sc.c_id
union
select t.*,sc.c_id cid2
from (select *
      from student
         , (select c_id from score where s_id = '01') t) t
         right join score sc
                   on t.s_id = sc.s_id
                       and t.c_id = sc.c_id
  • 从上述结果中判断cid2中没有空值的学生即为结果
    tips:可以先将之前的表保存为一个临时表或者视图,我这里就直接写一起了。最后记得剔除01的学生,题目并不要求查询01.
select *
from student
where s_id not in 
  (select s_id
   from (select t.*, sc.c_id cid2
         from (select *
               from student
                  , (select c_id from score where s_id = '01') t) t
                  left join score sc
                            on t.s_id = sc.s_id
                                and t.c_id = sc.c_id
         union
         select t.*, sc.c_id cid2
         from (select *
               from student
                  , (select c_id from score where s_id = '01') t) t
                  right join score sc
                             on t.s_id = sc.s_id
                                 and t.c_id = sc.c_id) tt
   where cid2 is null
   group by s_id)
and s_id != '01'

查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩


select stu.s_id,stu.s_name, ifnull(avg(score.s_score),0)
    from student stu left join score
on  stu.s_id = score.s_id
    group by stu.s_id
having sum(case when score.s_score>=60  then 0 else 1 end) >=2

2

评论区