sql经典45题详解亲测可用版带结果----31至45题

有很多同学是只看了前面的几道题，所以我倒序整理，先写后面15个题目。

后续篇章链接：https://blog.csdn.net/s_xing/article/details/106589779

练习数据

--1.学生表 Student(SId,Sname,Sage,Ssex) --SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--2.课程表 Course(CId,Cname,TId) --CId --课程编号,Cname 课程名称,TId 教师编号

--3.教师表 Teacher(TId,Tname) --TId 教师编号,Tname 教师姓名

--4.成绩表 SC(SId,CId,score) --SId 学生编号,CId 课程编号,score 分数

 

练习题目

31.  查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

思路：在SC里查询出01课程在80分以上的sid，关联学生表得到姓名

答案：

select
sc.sid, s.sname, sc.score
from
sc inner join student s
on sc.sid = s.sid
where sc.cid='01' and score>=80;

32.  求每门课程的学生人数

思路：在SC里对cid做聚合计算

答案：

select
cid, count(1)
from sc
group by cid;

33.  成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

思路：在SC作为基本表cid关联到course里面的tid，再关联到teacher表里面得到tname，就可以筛选得到张三老师的学生们sid的集合，再关联到student表就可以得到学生的信息。然后依据score做降序排列第一个人就是最高成绩者。

答案：

select *
from 
sc inner join course c on sc.cid = c.cid
inner join teacher t on c.tid = t.tid
inner join student s on s.sid = sc.sid
where t.tname = '张三' 
order by sc.score desc limit 1;

34.  成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

思路：在SC作为基本表cid关联到course里面的tid，再关联到teacher表里面得到tname，就可以筛选得到张三老师的学生们sid的集合，再关联到student表就可以得到学生的信息。然后依据score做降序排列我们得到张老师学生的排序表T了，现在要处理成绩重复的情况。

在T表的基础上，我们增加两个变量 rank记录排名，从0开始；score记录上一个同学的成绩，从-1开始；就可以比较当前和上一个的成绩了，出现变化则rank+1.这样就得到每个同学的排名X表。从X表容易筛选得到排名为1的同学集。

答案：

select * from 
(
        select T.*,
            @rank:=(case when @sco=score then @rank else @rank+1 end) as rn,
            @sco:=score as scoreRecord

            from 
                        (select t.tname, sc.cid, sc.score, s.*
                                from 
                                sc inner join course c on sc.cid = c.cid
                                inner join teacher t on c.tid = t.tid
                                inner join student s on s.sid = sc.sid
                                where t.tname = '张三' 
                                order by sc.score desc limit 5) T 
                        inner join (select @rank:=0, @sco:=-1) ext -- 千万别忘了起别名
        ) X
where rn < 2;

35.  查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

思路：在SC里做自关联，要求是同一个学生，不同科目，相同成绩的才关联成功。这时候一共会有C23，6个条目其中有重复的，我们通过group by 去重。

答案：

select a.sid, a.cid, a.score
from sc a inner join sc b
on a.sid = b.sid and a.cid != b.cid and a.score = b.score
group by a.sid, a.cid, a.score;

36.  查询每门功成绩最好的前两名

思路：在SC里排序，cid首个排序关键字，score次排序关键字（降序）。在这个基础上给每个同学附加一个排名信息。需要用到变量rank记录排名，cidR记录上一条的课程名字。如果cidR没变说明是同一门课程rank增长，如果cidR变了说明是新的课程了，rank赋值1.有了每个同学的排名信息再此基础之上筛选前二名即可。

答案：

select sid, cid, score, rn
from 
        (select 
        sc.*, 
        @rank:=if(@cidR=cid, @rank+1, 1) as rn,
        @cidR:=cid as cidR 
        from sc, (select @rank:=0, @cidR:=null) ext
        order by cid, score desc) X
where rn < 3;

37.  统计每门课程的学生选修人数（超过 5 人的课程才统计）

思路：在SC里对cid分类聚合并且只筛出其中数目大于5的情况。

答案：

select 
cid, count(1)
from sc
group by cid
having count(1)>5;

38.  检索至少选修两门课程的学生学号

思路：在SC里对sid分类聚合并且只筛出其中数目大于2的情况。

答案：

select sid
from sc
group by sid
having count(1) >= 2;

39.  查询选修了全部课程的学生信息

思路：在SC里对sid进行聚合并计数，筛选出其中计数为3的同学。其中的3又需要通过对cid计数得到。

答案：

select 
stu.*
from sc inner join student stu
on sc.sid=stu.sid
group by sc.sid
having count(1)>=(select count(1)
from (select distinct cid from sc) b);

40.  查询各学生的年龄，只按年份来算

思路：year（）函数抽取年份信息，now（）函数获得当前时间信息

答案：

select 
sid, sname, year(now())-year(sage) as nianling, sage
from student;

41.  按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

思路：timestampdiff（）函数里面传递year，得到年差距，这里的计算包含了去尾

答案：

select 
sid, sname, sage, timestampdiff(year, sage, curdate()) as accurateAge
from student;

42.  查询本周过生日的学生

思路：week函数得到当前date的week数，字符串拼接可以得到date数据。年末的情况没考虑

答案：

select 
sid, 
sname,
sage,
curdate(),
concat(year(curdate()), '-', month(sage), '-', day(sage)) as birthdayThisYear
from student
where week("2020-07-03") = week(concat(year(curdate()), '-', month(sage), '-', day(sage)));

43.  查询下周过生日的学生

思路：week函数得到当前date的week数，字符串拼接可以得到date数据。

答案：

select 
sid, 
sname,
sage,
curdate(),
concat(year(curdate()), '-', month(sage), '-', day(sage)) as birthdayThisYear
from student
where week("2020-06-25") = week(concat(year(curdate()), '-', month(sage), '-', day(sage)))-1;

45.  查询下月过生日的学生

思路：本年内的好算1月的下个月是2月，跨年的情况需要综合考虑year和month信息。

答案：

select *
from student
where month('2020-12-23')+1=month(sage) or month('2020-12-23')+1=month(sage)+12  -- 跨年