SQL面试50题笔记 11-20 详解思路与注意点

Student学生表：学号s_id 学生姓名s_name 出生年月 性别
Score 成绩表：学号s_id 课程编号c_id 成绩s_score
Course课程表：课程编号c_id 课程名称c_name 教师号t_id
Teacher 教师表：教师号t_id 教师姓名t_name

十一、查询至少有一门课与学号为’‘01’'的学生所学课程相同的学生的学号和姓名（重点）

select s_id s_name from student 
where s_id in(
select distinct s_id from score --- distinct 去重
where c_id in(
select c_id from score where s_id = '01' --- 学号为01学生选的课
) and s_id!=01 ---去除01学生本人
)

十二、 查询和01号同学所学课程完全相同的其他同学的学号（重点）
思路：

1. 选出01同学所学所有课程编号
2. 选出所学课程不在上面编号的同学，并排除掉
3. 再判断剩下的同学 所学的课程数是否和01同学选课数相同

select * from student
where s_id in (
select s_id from score
where s_id != '01' ---去除01学生本人
group by s_id having count(distinct c_id = (select count(distinct c_id)from score where s_id = '01'))---所学的课程数是否和01同学选课数相同
) and s_id is not in( ---排除掉这些所学课程和01学生不同的同学
select distinct s_id from score ---所学课程和01学生不同的同学
where c_id not in(
select c_id from score where s_id = '01' ---学号为01学生选的课
)
)

十五、查询两门及以上不及格课程的同学的学号、姓名和平均成绩（重点）

select s_id, s_name from student as st
inner join(
select s_id,avg(s_score)from score 
where s_score<60
group by s_id
having count(distinct s_score)>=2
) as sc on st.s_id on sc.s_id

十六、检索01课程分数小于60，并按分数降序排列学生信息

select * from student as st
inner join(
select * from score where s_score = '01' and s_score < 60
) as sc on st.s_id = sc.s_id
order by sc.s_score desc

注意表之间的对应关系，如果是多对多的表inner join可能会产生笛卡尔积

十七、按平均成绩从高到低实现所有学生的所有课程的成绩以及平均成绩
法一：

select s.s_id, s.c_id, s.s_score, b.avg_score from score as s
inner join(
select s_id, avg(s_score) as avg_score from score group by s_id
)as b on s.s_id = b.s_id
order by b.avg_score desc

这种做法展现出来的样式并不是最好的，行列转换成下面的形式：

当使用group by，加入未出现的列时，mysql不会报错，但是那一列的值没有意义：

行转列的操作
case when，和统计函数的灵活运用（一门课的成绩只有一条，所以取max没有任何影响）

select s_id "学号"
,max(case when c_id = '01' then s_score else null end) "语文"
,max(case when c_id = '02' then s_score else null end) "数学"
,max(case when c_id = '03' then s_score else null end) "英语"
,avg(s_score) "平均成绩"
from score
group by s_id
order by avg(s_score) desc

十八、查询各科成绩最高分、最低分、平均分，以如下形式显示：课程id，课程name，最高分、最低分、平均分、及格率、中等率、优良率、优秀率（及格大于等于60、中等70-80，优良80-90，优秀大于等于90）

select s.c_id, c.c_name, max(s.s_score), min(s.s_score), avg(s.s_score)
,sum(case when s.s_score >= 60 then 1 else 0 end)/count(s_id) --- 使用case when和sum
,sum(case when s.s_score >= 70 and s.s_score <80 then 1 else 0 end)/count(s_id) 
,sum(case when s.s_score >= 80 and s.s_score <90 then 1 else 0 end)/count(s_id) 
,sum(case when s.s_score >= 90then 1 else 0 end)/count(s_id) 
from score as s
inner join course as c on s.c_id = c.c_id
group by c_id

十九、开窗函数
row_number(), rank(), dense_rank ()的用法和区别:https://blog.csdn.net/u011726005/article/details/94592866
partition by: 按xx分组
order by： 按xx排序

group by 和这些的区别：groupby在分组的基础上输出统计量，不会每一条数据都原封不动地保留

二十、查询学生总成绩并进行排名

select s_id, sum(s_score) as sum_score 
from score
group by s_id
order by sum_score desc