OVER(PARTITION_BY)函数介绍.

--学生表
create table st1(id number primary key,st_name varchar2(10));
--课程表
create table course1(id number primary key,course_name varchar2(8));
--成绩表
create table score(id number primary key,st_id number,course_id number,score number,foreign key

(st_id) references st1(id),foreign key(course_id) references course1(id));

--
insert into st1 values(1,'aom');
insert into st1 values(2,'bom');
insert into st1 values(3,'com');
insert into st1 values(4,'dom');
insert into st1 values(5,'eom');
insert into st1 values(6,'fom');
insert into st1 values(7,'fom');

--
insert into course1 values(1,'语文');
insert into course1 values(2,'数学');
insert into course1 values(3,'英语');

--
insert into score values(1,1,1,100);
insert into score values(2,1,2,99);
insert into score values(3,1,3,98);
insert into score values(4,2,1,97);
insert into score values(5,2,2,96);
insert into score values(6,2,3,95);
insert into score values(7,3,1,94);
insert into score values(8,3,2,93);
insert into score values(9,3,3,92);
insert into score values(10,4,1,91);
insert into score values(11,4,2,90);
insert into score values(12,4,3,89);
insert into score values(13,5,1,88);
insert into score values(14,5,2,87);
insert into score values(15,5,3,86);
insert into score values(16,6,1,85);
insert into score values(17,6,2,84);
insert into score values(18,6,3,83);
insert into score values(19,7,1,100);
insert into score values(20,7,2,99);
insert into score values(21,7,3,77);

--1.选出每门成绩排名前三名学生的姓名、课程名、分数(并列时名次会一样，后面的排名会跳过排如1,1,3)
select * from
     (select a4.* ,rank()over(partition by  a4.course_name order by a4.score desc) a5 from
          (select a1.st_name,a2.course_name,a3.score from st1 a1,course1 a2,score a3
           where a1.id=a3.st_id and a2.id=a3.course_id
          ) a4
     )
where a5<=3;
--或（并列时按顺序排如1,2,3）
select * from
     (select a4.* ,row_number()over(partition by  a4.course_name order by a4.score desc) a5 from
          (select a1.st_name,a2.course_name,a3.score from st1 a1,course1 a2,score a3
           where a1.id=a3.st_id and a2.id=a3.course_id
          ) a4
     )
where a5<=3;