问个sql面试题

[amdgaming]

现在有三个表student,score,course
对应的建表语句如下：
CREATE TABLE student(
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))

要求用基本SQL实现如下的两条查询要求：
(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名
(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名

[amdgaming]

只能发100分
大家帮忙 写写。。。

[虫洞]

懂用row_number() over()就可以了

[虫洞]

select *
from
(
select a.*
,row_number() over(partition by course_id order by score desc) rn
from score a
)
where rn<=5
;

[秋雨飘落]

[Quote=引用 3 楼 canhui87 的回复:]
select *
from
(
select a.*
,row_number() over(partition by course_id order by score desc) rn
from score a
)
where rn<=5
;
[/Quote]
简单明了

[tangren]

--如果即使分数相等，也只取5位学生，使用row_number()，
--如果允许并列，且允许名次不连续，使用rank(),
--如果允许并列，且名次连续，使用dense_rank()

--1.
--法1
SELECT st.student_name, sc.score, co.course_name, rnk
  FROM (SELECT t.*,
               dense_rank() over(PARTITION BY t.course_id ORDER BY t.score DESC) rnk
          FROM score t) sc,
       student st,
       course co
 WHERE sc.student_id = st.student_id
   AND sc.course_id = co.course_id
   AND rnk <= 5;

--法2
SELECT *
  FROM score sc, student st, course co
 WHERE sc.student_id = st.student_id
   AND sc.course_id = co.course_id
   AND 5 > (SELECT COUNT(*)
              FROM score t
             WHERE t.course_id = sc.course_id
               AND t.score > sc.score)

--2.
SELECT st.student_name, sc.score, co.course_name, rnk
  FROM (SELECT t.*,
               dense_rank() over(PARTITION BY t.course_id ORDER BY t.score DESC) rnk
          FROM score t) sc,
       student st,
       course co
 WHERE sc.student_id = st.student_id
   AND sc.course_id = co.course_id
   AND rnk = 3;