别人的整理:
Oracle:点击打开链接
我的整理:
基础表:
1.计算每个人的总成绩并排名(要求显示字段: 学号,姓名,总成绩)
select stuid,name,sum(score)
from stuscore
group by stuid,name
order by sum(score) desc
2.计算每个人单科的最高成绩(要求显示字段: 学号,姓名,课程,最高成绩)
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