sql - 关于学习成绩统计

别人的整理：

mysql统计－关于学生成绩：点击打开链接

Oracle：点击打开链接

我的整理：

基础表：

1.计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)

select stuid,name,sum(score)
from stuscore
group by stuid,name
order by sum(score) desc

2.计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

<pre name="code" class="sql">select T1.stuid,T1.name,T1.subject,T1.score
from stuscore T1, 
 (
     select name ,Max(score) max_s 
     from stuscore 
     group by name
 ) T2
where  T1.score = T2.max_s and T1.name = T2.name
-----------------------------------------------------下面是另一种写法
select T1.stuid,T1.name,T1.subject,T1.score
from stuscore T1 join
 (
     select name ,Max(score) max_s 
     from stuscore 
     group by name
 ) T2
on T1.name = T2.name
where T1.Score = T2.max_s

3.计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

select stuid,name,round(avg(score)) 平均成绩
from stuscore
group by stuid,name
order by 平均成绩 desc
---------------------------------------------------
select distinct t1.stuid,t1.name,t2.平均成绩 
from stuscore t1,
 (
  select stuid,round(avg(score)) as 平均成绩 
  from stuscore 
  group by stuid
 ) t2 
 where t1.stuid=t2.stuid
 order by t2.平均成绩 desc

4.列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

select t1.*
from stuscore t1,
(
  select subject,MAX(score) max_s
  from stuscore
  group by subject
) t2
where t1.score = t2.max_s and t1.subject = t2.subject

5.列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）

select t2.* from
  (
    select t1.*,rownum 排名 from 
    (
      select *from stuscore
      where subject = '数学'
      order by score desc
    )t1
    where rownum<=3
  ) t2
  where t2.排名>=2

6.求出李四的数学成绩的排名 

select t2.* from
(
  select t1.*,rownum 排名 
  from stuscore t1
)t2
where t2.subject='数学' and t2.name='李四'

7.列出各门课程成绩最好的两位学生

select * from 
(
       select name,subject,score, row_number() over(partition by stuscore.subject order by stuscore.score desc) 排名 
       from stuscore
) 
where 排名<3;

经测试此处将row_number()替换成rank()结果一样

over的作用及用法:点击打开链接

8.统计如下：学号     姓名     语文     数学     英语     总分     平均分

<pre name="code" class="sql">即纵表转横表：
select stuid,name,
sum(case subject when '语文' then score else 0 end ) 语文,
sum(case subject when '数学' then score else 0 end ) 数学,
sum(case subject when '英语' then score else 0 end ) 英语,
sum(score) 总分,
round(avg(score),2) 平均分
from stuscore
group by stuid,name
order by 平均分 desc

关于纵表与横表互转的SQL：点击打开链接

<pre name="code" class="sql">先创建一个横表：（表的结构及数据即为上图）
create table h_stuscore as
select * from
(
  select stuid,name,
  sum(case subject when '语文' then score else 0 end ) 语文,
  sum(case subject when '数学' then score else 0 end ) 数学,
  sum(case subject when '英语' then score else 0 end ) 英语,
  sum(score) 总分,
  round(avg(score),2) 平均分
  from stuscore
  group by stuid,name
  order by 平均分 desc
)
--------------------------------
横标转纵表：（又转为最开始的基础表了）

select stuid,name,'语文' as subject, 语文 as score from h_stuscore union all
select stuid,name,'数学' as subject, 数学 as score from h_stuscore union all
select stuid,name,'英语' as subject, 英语 as score from h_stuscore 
order by stuid

9．统计课程不及格（0-59）、 良（60-80）、优（81-100）的个数

select subject,   
(select count(*) from stuscore where score<60 and subject=t1.subject) as 不及格,  
(select count(*) from stuscore where score between 60 and 80 and subject=t1.subject) as 良, 
(select count(*) from stuscore where score >80 and subject=t1.subject) as 优  
from stuscore t1 
group by subject

 

10.计算科科及格的人的平均成绩 

select stuid,name,round(avg(score),2) 平均成绩
from stuscore
where score>=60 and name not in
(
      select name from stuscore where score<60
)
group by stuid,name
order by 平均成绩 desc
----------------------------------------------------下面是另一种更好的写法
select stuid,name,round(avg(score),2) 平均成绩
from stuscore
group by stuid,name
having min(score) >= 60
order by 平均成绩 desc