sql经典40题（下）

发布时间：2024-11-19 07:11

发现并模仿经典电影或游戏的主题曲，回味经典 #生活乐趣# #日常生活趣事# #音乐欣赏的乐趣# #趣味音乐#

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

select s.sid, sname, avg_score

from (

select sid, count(if(score<60, score, null)) count, avg(score) avg_score

from SC

group by sid

having count(if(score<60, score, null)) >= 2

) t

join Student s

on s.sid = t.sid;

27. 检索"01"课程分数小于60，按分数降序排列的学生信息

select sid, sname, sage

from Student

where sid in (

select sid

from SC

where cid = '01'and score < 60

order by score desc

);

select s.sname, s.sid, s.sage, sc.score

from Student s right join (

select sid, score

from SC

where cid = '01' and score < 60

) sc

on sc.sid = s.sid;

网址：sql经典40题（下） https://www.yuejiaxmz.com/news/view/132795