网络热门sql50题，每天5道（2月20号）(16-20)

 #SQL 16--检索"01"课程分数小于60，按分数降序排列的学生信息

#自连接
select
    b.*
from score as a,student as b
where a.s_id = b.s_id
and a.c_id = '01'
and a.s_score < 60
order by a.s_score desc;

#右连接
select
    b.*,a.s_score
from score as a
right join
    student as b
on a.s_id = b.s_id
where a.c_id = '01'
and a.s_score < 60
order by a.s_score desc;

#SQL 17——按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

#先计算每位同学的平均成绩
select
    b.*,
    avg(a.s_score) as avg_score
from score as a
right join
    student as b
on a.s_id = b.s_id
group by b.s_id
order by avg_score desc;

#再查出所有的成绩
select * from score;

#连接两张表
select
    t2.*,
    t1.c_id,
    t1.s_score
from (select * from score) as t1,
     (select
          b.*,
          round(avg(a.s_score),2) as avg_score
      from score as a
               right join
           student as b
           on a.s_id = b.s_id
      group by b.s_id
      order by avg_score desc) as t2
where t1.s_id = t2.s_id;

-- mysql 8.0后加入了开窗函数(可用于聚合函数)(要8.0以上)
select
    a.*,
    avg(a.s_score) over (partition by a.s_id) as avg_score
from score as a;

#SQL 18——查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程名称，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。及格为 60-70，中等为 71-80，优良为 81-90，优秀为 >= 91。

#count(1) 会返回总行数
select
    a.c_id,
    a.c_name,
    max(b.s_score) as max_score,
    min(b.s_score) as min_score,
    avg(b.s_score) as avg_score,
    sum(case when b.s_score >= 60 and b.s_score < 70 then 1 else 0 end) / count(1) as jige,
    sum(case when b.s_score >=70 and b.s_score < 80 then 1 else 0 end)  / count(1) as zhongdeng,
    sum(case when b.s_score >=80 and b.s_score < 90 then 1 else 0 end)  / count(1) as youliang,
    sum(case when b.s_score >=90 then 1 else 0 end)  / count(1) as youxiu
from
    course as a
left join score as b
on a.c_id = b.c_id
group by a.c_id;

#SQL 19——按各科成绩进行排序，并显示排名，成绩重复时合并名次

#开窗函数
select
    a.*,
    rank() over(partition by c_id order by s_score desc) as rk
from
    score as a ;

#SQL 20——查询学生的总成绩并进行排名，总分重复时不保留名次空缺 (安装mysql8.0)

#根据学号分组查询总成绩
select
    t.*,
    rank() over(order by sum_score desc) as rk
from
    (select
         a.s_id,
         sum(a.s_score) as sum_score
     from
         score as a
     group by a.s_id) as t;




select
    a.s_id,
    sum(a.s_score) as sum_score,
    rank() over (order by sum(a.s_score) desc) as rk
from
    score as a
group by a.s_id