网上碰到过一个oracle数据库的面试题,如下:
有如下表:student_score,
ID STUDENT SCORE TYPE
1 tom 90 语文
2 tom 91 数学
3 tom 89 英语
4 jim 92 语文
5 jim 85 数学
6 jim 86 英语
要求:读取每个科目的最高分。
这个题目要解答的话有两种方式。
1、直接读取每个科目的最高分,然后取并集
select s1.student, s1.type, s1.score
from student_score s1
where s1.type = '语文'
and s1.score = (select max(s1.score)
from student_score s1
where s1.type = '语文')
union all
select s1.student, s1.type, s1.score
from student_score s1
where s1.type = '数学'
and s1.score = (select max(s1.score)
from student_score s1
where s1.type = '数学')
union all
select s1.student, s1.type, s1.score
from student_score s1
where s1.type = '英语'
and s1.score = (select max(s1.score)
from student_score s1
where s1.type = '英语');
结果如下,
STUDENT TYPE SCORE
jim 语文 92
tom 数学 91
tom 英语 89
2、使用over函数
select *
from (select student,
type,
score,
rank() over(partition by type order by score desc) mm
from student_score s)
where mm = 1;
结果:
STUDENT TYPE SCORE MM
tom 数学 91 1
tom 英语 89 1
jim 语文 92 1
显然,over函数实现更简洁。
如上展示的是over的分组。
如下读取,
select *
from (select student,
type,
score,
rank() over(partition by type order by score desc) mm
from student_score s)
结果:
STUDENT TYPE SCORE MM
tom 数学 91 1
jim 数学 85 2
tom 英语 89 1
jim 英语 86 2
jim 语文 92 1
tom 语文 90 2
将每个科目的名次也展示了。
在一些统计的语句中可该函数可以帮助我们进行复杂数据的统计显示和计算。