oracle按照成绩排序,oracle 经典分数排名

Sql代码

createtableSCORE

(

SCORE_ID NUMBERnotnull,

STUDENT_ID NUMBER,

COURSE_ID NUMBER,

SCORE NUMBER

)

;

altertableSCORE

addprimarykey(SCORE_ID);

prompt Loading SCORE...

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(1, 1, 1, 99);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(2, 1, 2, 98);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(3, 1, 3, 97);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(4, 2, 1, 99);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(5, 2, 2, 97);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(6, 2, 3, 98);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(7, 3, 1, 96);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(8, 3, 2, 95);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(9, 3, 3, 94);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(10, 4, 1, 93);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(11, 4, 2, 92);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(12, 4, 3, 91);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(13, 5, 1, 90);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(14, 5, 2, 89);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(15, 5, 3, 88);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(16, 6, 1, 87);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(17, 6, 2, 86);

insertintoSCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)

values(18, 6, 3, 85);

commit;

create table SCORE ( SCORE_ID NUMBER not null, STUDENT_ID NUMBER, COURSE_ID NUMBER, SCORE NUMBER ) ; alter table SCORE add primary key (SCORE_ID); prompt Loading SCORE... insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (1, 1, 1, 99); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (2, 1, 2, 98); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (3, 1, 3, 97); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (4, 2, 1, 99); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (5, 2, 2, 97); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (6, 2, 3, 98); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (7, 3, 1, 96); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (8, 3, 2, 95); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (9, 3, 3, 94); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (10, 4, 1, 93); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (11, 4, 2, 92); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (12, 4, 3, 91); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (13, 5, 1, 90); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (14, 5, 2, 89); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (15, 5, 3, 88); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (16, 6, 1, 87); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (17, 6, 2, 86); insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (18, 6, 3, 85); commit;

(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名：

根据不同的排名方式有三种不同的sql写法：

1.1成绩相同的人排名相同，且排名是连续的。

Sql如下：

Sql代码

select*

from(selects.STUDENT_NAME,

sc.SCORE,

c.COURSE_NAME,

dense_rank() over(partitionbyc.COURSE_IDorderbysc.SCOREdesc) drank

fromstudent s, course c, score sc

wheres.STUDENT_ID = sc.STUDENT_ID

andc.COURSE_ID = sc.COURSE_ID) t

wheret.drank 

select * from (select s.STUDENT_NAME, sc.SCORE, c.COURSE_NAME, dense_rank() over(partition by c.COURSE_ID order by sc.SCORE desc) drank from student s, course c, score sc where s.STUDENT_ID = sc.STUDENT_ID and c.COURSE_ID = sc.COURSE_ID) t where t.drank < 6;

结果如下：

STUDENT_NAME SCORE COURSE_NAME DRANK

张三 99 语文 1

李四 99 语文 1

王五 96 语文 2

马六 93 语文 3

孙七 90 语文 4

王八 87 语文 5

张三 98 数学 1

李四 97 数学 2

王五 95 数学 3

马六 92 数学 4

孙七 89 数学 5

李四 98 英语 1

张三 97 英语 2

王五 94 英语 3

马六 91 英语 4

孙七 88 英语 5

1.2成绩相同的人排名相同，且排名不是连续的。

Sql如下：

Sql代码

select*

from(selects.STUDENT_NAME,

sc.SCORE,

c.COURSE_NAME,

rank() over(partitionbyc.COURSE_IDorderbysc.SCOREdesc) ranking

fromstudent s, course c, score sc

wheres.STUDENT_ID = sc.STUDENT_ID

andc.COURSE_ID = sc.COURSE_ID) t

wheret.ranking 

select * from (select s.STUDENT_NAME, sc.SCORE, c.COURSE_NAME, rank() over(partition by c.COURSE_ID order by sc.SCORE desc) ranking from student s, course c, score sc where s.STUDENT_ID = sc.STUDENT_ID and c.COURSE_ID = sc.COURSE_ID) t where t.ranking < 6;

结果如下：

STUDENT_NAME SCORE COURSE_NAME RANKING

张三 99 语文 1

李四 99 语文 1

王五 96 语文 3

马六 93 语文 4

孙七 90 语文 5

张三 98 数学 1

李四 97 数学 2

王五 95 数学 3

马六 92 数学 4

孙七 89 数学 5

李四 98 英语 1

张三 97 英语 2

王五 94 英语 3

马六 91 英语 4

孙七 88 英语 5

1.2成绩相同的人根据学号排序，排名是连续的。

Sql如下：

Sql代码

select*

from(selects.STUDENT_NAME,

sc.SCORE,

c.COURSE_NAME,

row_number() over(partitionbyc.COURSE_IDorderbysc.SCOREdesc, s.STUDENT_ID) rn

fromstudent s, course c, score sc

wheres.STUDENT_ID = sc.STUDENT_ID

andc.COURSE_ID = sc.COURSE_ID) t

wheret.rn 

select * from (select s.STUDENT_NAME, sc.SCORE, c.COURSE_NAME, row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn from student s, course c, score sc where s.STUDENT_ID = sc.STUDENT_ID and c.COURSE_ID = sc.COURSE_ID) t where t.rn < 6;

结果如下：

STUDENT_NAME SCORE COURSE_NAME RN

张三 99 语文 1

李四 99 语文 2

王五 96 语文 3

马六 93 语文 4

孙七 90 语文 5

张三 98 数学 1

李四 97 数学 2

王五 95 数学 3

马六 92 数学 4

孙七 89 数学 5

李四 98 英语 1

张三 97 英语 2

王五 94 英语 3

马六 91 英语 4

孙七 88 英语 5

(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名：

Sql如下：

Sql代码

select*

from(selects.STUDENT_NAME,

sc.SCORE,

c.COURSE_NAME,

row_number() over(partitionbyc.COURSE_IDorderbysc.SCOREdesc, s.STUDENT_ID) rn

fromstudent s, course c, score sc

wheres.STUDENT_ID = sc.STUDENT_ID

andc.COURSE_ID = sc.COURSE_ID) t

wheret.rn = 3;

select * from (select s.STUDENT_NAME, sc.SCORE, c.COURSE_NAME, row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn from student s, course c, score sc where s.STUDENT_ID = sc.STUDENT_ID and c.COURSE_ID = sc.COURSE_ID) t where t.rn = 3;

结果如下：

STUDENT_NAME SCORE COURSE_NAME RN

王五 96 语文 3

王五 95 数学 3

王五 94 英语 3

STUDENT_NAME SCORE COURSE_NAME RN

王五 96 语文 3

王五 95 数学 3

王五 94 英语 3