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

书接上回  https://blog.csdn.net/s_xing/article/details/106588513

练习数据

--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 分数

 

练习题目

15.  ①按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

①思路：啥叫保留名次空缺，我们不去纠结。我们把重点放在sql语法上，这里的重点是使用变量，我们按照score降序排列，然后需要一个rank变量，每次自加1，这样就得到每个条目的名次信息。

①答案：

select 
sid, cid, score, @rank:=@rank+1 as simpleMingci
from sc, (select @rank:=0, @sco:=-1) ext
order by score desc;

②按各科成绩进行排序，并显示排名， Score 重复时合并名次

②思路：也就是成绩出现变化时才增加rank数值，需要一个辅助的变量scoreR(记录上一个条目的成绩),我们需要两个变量，而且我们需要使用if语句。

②答案：

select 
sid, cid, score, @rank:=if(@sco=score,@rank,@rank+1) as simpleMingci,
                            @sco:=score as scoreR
from sc, (select @rank:=0, @sco:=-1) ext
order by score desc;

16. ① 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

1 思路：在SC里针对sid进行分组聚合计算得到每个学生的总成绩，然后降序排列。这就是我们要处理的基础表T。保留名次空缺，懂了，就是同成绩的时候弄成null，不占用一个名次。

1 答案：

select t.*, @rank:=if(@sco=totalScore,null,@rank+1) as mingci
from (select sid, sum(score) as totalScore
                from sc
                group by sid
                order by totalScore desc) t, (select @rank:=0, @sco:=0) b;

②查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

思路： 比第一问容易

答案：

select t.*, @rank:=(@rank+1)
from (select sid, sum(score) as totalScore
                from sc
                group by sid
                order by totalScore desc) t, (select @rank:=0, @sco:=0) extra

17.  统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

思路：sum函数和case when 函数的用法。百分比的计算：concat函数可以直接连接数字和字符串

答案：

select 
cid,
count(1) as zongRenshu,
sum(case when score>=0 and score<60 then 1 else 0 end) as 60Renshu,
sum(case when score>=60 and score<75 then 1 else 0 end) as 70Renshu,
sum(case when score>=75 and score<85 then 1 else 0 end) as 85Renshu,
sum(case when score>=85 and score<=100 then 1 else 0 end) as 100Renshu,
concat(sum(case when score>=0 and score<60 then 1 else 0 end)/count(1)*100,'%') as 60RenshuRate,
concat(sum(case when score>=60 and score<75 then 1 else 0 end)/count(1)*100,'%') as 70RenshuRate,
concat(sum(case when score>=75 and score<85 then 1 else 0 end)/count(1)*100,'%') as 85RenshuRate,
concat(sum(case when score>=85 and score<=100 then 1 else 0 end)/count(1)*100,'%') as 100RenshuRate
from sc
group by cid;

18.  查询各科成绩前三名的记录

思路：嵌套查询，对于每一个条目我们把它作为外层_outBiao条目，筛选条件：比他成绩好的同学少于3个。在where的内部我们可以用到外部_outBiao里面的cid和score就可以找到比_outBiao成绩好的数量。

为了好看我们在完成初步筛选之后再外层搞一个order，方便观察。

答案：

select *
from 
        (select 
        sid,
        cid ,
        score 
        from sc _outBiao
        where (select count(1) from sc where cid=_outBiao.cid and score>_outBiao.score) < 3) rawTable
order by cid, score desc;

19.  查询每门课程被选修的学生数

思路：按照cid分类聚合计算count

答案：

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

20.  查询出只选修两门课程的学生学号和姓名

思路：对sc按照cid分类聚合计数，然后结合join得到sname

答案：

select a.sid, b.sname
from (select
                sc.sid
                from sc
                group by sc.sid
                having count(1)=2) a inner join student b
on a.sid = b.sid;

21.  查询男生、女生人数

思路：按照ssex分类聚合计数

答案：

select ssex, count(1)
from student
group by ssex;

22.  查询名字中含有「风」字的学生信息

思路：字符串匹配，like关键字，%通配符

答案：

select *
from student
where sname like '%风%'

23.  查询同名同性学生名单，并统计同名人数

思路：先要找到同名同性的条目，为了找到这些需要做自关联，然后找到sid不同，sname，ssex相同作为关联条件。

在那些条目基础上聚合计数就能统计出来了。

答案：

select 
a.sname, a.ssex, count(1) -- 只能是group by里面的关键字
from student a inner join student b
on a.sname = b.sname
where a.sid != b.sid and a.ssex = b.ssex
group by a.sname, a.ssex;

24.  查询 1990 年出生的学生名单

思路：year函数的用法

答案：

select
*
from student
where year(sage) = '1990' -- year 是一个函数

25.  查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

思路：先聚合再排序

答案：

select 
cid, avg(score) as avgScore
from sc
group by cid 
order by avgScore desc, cid asc;

26.  查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

思路：按照sid聚合计算平均分，然后对平均分进行筛选。

答案：

select
a.sid, b.sname, avg(score) as avgScore
from
sc a inner join student b
on a.sid = b.sid
group by sid
having avg(score) >= 85;

27.  查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

思路：sc关联course得到cname，关联student得到姓名然后where筛选

答案：

select 
*
from
sc left join course c on sc.cid = c.cid
left join student s on sc.sid = s.sid
where c.cname = '数学' and sc.score < 60;

28.  查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

思路：所有学生都要，所以学生左关联课程

答案：

select
s.sid, s.sname, sc.cid, sc.score -- , sum(case when sc.cid=null then 0 else 1 end) as cCourse, avg(score) as avgScore
from student s
left join sc
on s.sid = sc.sid;

29.  查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

思路：sc关联course得到cname，关联student得到姓名然后where筛选

答案：

select
s.sname, c.cname, sc.score
from 
sc inner join student s on sc.sid = s.sid
inner join course c on sc.cid = c.cid
where sc.score >= 70;

30.  查询不及格的课程

思路：筛选score<60的cid，注意distinct去重

答案：

select * from course
where cid in
(
select distinct cid
from sc
where score < 60
);