oracle分组排序

在做统计是很多情况会用到分组排序 结合http://blog.csdn.net/shilang999/article/details/6398104谈论的方法

 

自己建了一个demo验证 留在这里以便备份

CREATE TABLE student2(
student_id NUMBER PRIMARY KEY,
student_name VARCHAR2(30) NOT NULL)

CREATE TABLE score(
score_id NUMBER PRIMARY KEY,
student_id NUMBER,
course_id NUMBER,
score NUMBER)

CREATE TABLE course(
course_id NUMBER PRIMARY KEY,
course_name VARCHAR2(30)) ;

insert into course values (1,'课程1');
insert into course values (2,'课程2');
insert into course values (3,'课程3');
insert into course values (4,'课程4');

insert into score values (1,1,1,90);
insert into score values (2,1,2,89);
insert into score values (3,1,3,88);
insert into score values (4,1,4,85);
insert into score values (5,2,1,86);
insert into score values (6,2,2,80);
insert into score values (7,2,3,56);
insert into score values (8,2,4,98);
insert into score values (9,3,1,67);
insert into score values (10,3,4,34);
insert into score values (11,3,2,67);
insert into score values (12,3,3,90);

insert into score values (13,4,1,91);
insert into score values (14,4,2,92);
insert into score values (15,4,3,93);
insert into score values (16,4,4,94);
insert into score values (17,5,1,98);
insert into score values (18,5,2,99);
insert into score values (45,5,3,98);
insert into score values (46,5,4,99);

insert into score values (19,6,1,88);
insert into score values (20,6,2,89);
insert into score values (21,6,3,88);
insert into score values (22,6,4,89);

insert into score values (23,7,1,78);
insert into score values (24,7,2,79);
insert into score values (25,7,3,78);
insert into score values (26,7,4,79);

insert into score values (27,8,1,71);
insert into score values (28,8,2,72);
insert into score values (29,8,3,73);
insert into score values (30,8,4,74);

 

insert into score values (31,9,1,61);
insert into score values (32,9,2,62);
insert into score values (33,9,3,63);
insert into score values (34,9,4,64);

insert into score values (35,10,1,55);
insert into score values (36,10,2,65);
insert into score values (37,10,3,53);
insert into score values (38,10,4,54);

insert into student2 values (1,'张1');
insert into student2 values (2,'张2');
insert into student2 values (3,'张3');
insert into student2 values (4,'张4');
insert into student2 values (5,'张5');
insert into student2 values (6,'张6');
insert into student2 values (7,'张7');
insert into student2 values (8,'张8');
insert into student2 values (9,'张9');
insert into student2 values (10,'张10');

 

 

用rank() dense_rank()具有并列名次之分

select *
  from (select a.score,
               b.student_name,
               c.course_name,
               rank() over(partition by a.course_id order by a.score desc) rk
          from score a, student2 b, course c
          where a.student_id = b.student_id
           and a.course_id = c.course_id) ss
 where ss.rk <= 4

 

而采用row_number()是直接去行数  不管是否具有相同数据

select *
  from (select a.score,
               b.student_name,
               c.course_name,
               row_number() over(partition by a.course_id order by a.score desc) rk
          from score a, student2 b, course c
          where a.student_id = b.student_id
           and a.course_id = c.course_id) ss
 where ss.rk <= 4