SQL 不及格课程数大于2的学生的平均成绩及其排名

最新推荐文章于 2024-02-09 08:46:10 发布

原创最新推荐文章于 2024-02-09 08:46:10 发布 · 2.2k 阅读

1 ·

CC 4.0 BY-SA版权

我们不生产知识，我们只是互联网的搬运工

SQL 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了一种使用SQL进行学生成绩统计的方法，通过复杂的联接和子查询，实现了对成绩低于60分且科目数量大于等于2的学生平均成绩的计算，并在此基础上进行了排名。此方法适用于教育数据分析和学生表现评估。

数据

create table scores
(
  sid int,
  score int,
  cid int
);

insert into scores values
(1, 90, 1),
(1, 59, 2),
(1, 67, 3),
(2, 20, 1),
(2, 30, 2),
(2, 40, 3),
(3, 14, 1),
(3, 13, 2),
(3, 15, 3),
(4, 90, 1),
(4, 90, 2),
(4, 87, 3)
;

sql

select t1.sid, t1.average_score, count(distinct t2.sid) as rank_n
from 
(
  select sid, avg(score) as average_score
  from scores
  where sid in
  (
    select sid from scores
    where score <= 60
    group by sid
    having count(cid) >=2
  )
  group by sid
) t1
left join
(
  select sid, avg(score) as average_score
  from scores
  group by sid
) t2
on t1.average_score <= t2.average_score
group by t1.sid
order by t1.average_score desc
;

or 先排名再筛选

select t1.sid, t1.average_score, count(distinct t2.sid) as rank_n
from 
(
  select sid, avg(score) as average_score
  from scores
  group by sid
) t1
left join
(
  select sid, avg(score) as average_score
  from scores
  group by sid
) t2
on t1.average_score <= t2.average_score
where t1.sid in
(
  select sid from scores
  where score <= 60
  group by sid
  having count(cid) >=2
)
group by t1.sid
order by t1.average_score desc
;