SQL进阶练习题41-45
大背景和建表、插入语句就不啰嗦了，参考第一篇。
四张表概要：
- 学生表
student(sid,sname,sage,ssex) --sid 学生编号,sname 学生姓名,sage 出生年月,ssex 学生性别 - 课程表
course(cid,cname,tid) --cid 课程编号,cname 课程名称,tid 教师编号 - 教师表
teacher(tid,tname) --tid 教师编号,tname 教师姓名 -
成绩表
sc(sid,cid,score) --sid 学生编号,cid 课程编号,score 分数
为了方便查看，我把四个表截了图：
student
course
teacher
sc
题目：
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
- 查询每门功成绩最好的前两名
- 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
- 检索至少选修两门课程的学生学号
- 查询选修了全部课程的学生信息
sql
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select C.sid,max(C.cid)cid,max(C.score)score from sc C
left join(select sid,avg(score)A from sc group by sid)B
on C.sid=B.sid
where C.score=B.A
group by C.sid
having COUNT(1)=(select COUNT(1) from sc where sid=C.sid);
查询每门功成绩最好的前两名
select cid,score,row_number() over(partition by cid order by score desc) rank from sc
where rank<3 ;
统计每门课程的学生选修人数（超过 5 人的课程才统计）
select t.cid,max(t.rank) from
(select cid,row_number() over(partition by cid order by score) rank
from sc
where rank>=5) t
group by t.cid;
检索至少选修两门课程的学生学号
select distinct t.tid from
(select tid,row_number() over(partition by tid order by cid) rank
from sc
where rank>=2) t;
查询选修了全部课程的学生信息
select t.tid,td.* from
(select tid,row_number() over(partition by tid order by cid) rank
from sc
where rank=3) t
join student td on td.tid=t.tid;
