某企业SQL面试题求解

--(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名
SELECT  t.student_name, t.score, t.course_name
    FROM (SELECT ROWNUM NO, st.student_name, sc.score, co.course_name
            FROM student st, score sc, course co
           WHERE st.student_id = sc.student_id AND sc.course_id = co.course_id) t
   WHERE t.NO <= 5 * (SELECT SUM (num)
                        FROM (SELECT COUNT (DISTINCT course_name) AS num
                                FROM course co))
ORDER BY t.course_name, t.score DESC
--(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名
select t.student_name, t.score, t.course_name from(
SELECT  t.student_name, t.score, t.course_name,dense_rank() over (partition by t.course_name order by t.score) as rn
    FROM (SELECT ROWNUM NO, st.student_name, sc.score, co.course_name
            FROM student st, score sc, course co
           WHERE st.student_id = sc.student_id AND sc.course_id = co.course_id) t
   WHERE t.NO <= 3 * (SELECT SUM (num)
                        FROM (SELECT COUNT (DISTINCT course_name) AS num
                                FROM course co))
ORDER BY t.course_name, t.score DESC)t
where t.rn=1

我的比较难看点，总算可以用！～～

我记得这题我在csdn上做过

用程序逻辑解决不用1分钟。
单纯用标准SQL太复杂了，看得脑晕晕的，好吧，我承认面试官威武，我回家面壁思过去。

附T-SQL代码：
--(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名
declare @t int

declare cur cursor for
(
    select course_id from course
)
open cur
fetch next from cur into @t
while(@@fetch_status=0)
begin
    select top 5 a.student_name, b.score, c.course_name from student a, score b, course c  where  a.student_id = b.student_id AND c.course_id = b.course_id and b.course_id=@t order by b.score desc
    fetch next from cur into @t
end
close cur
deallocate cur


--(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名
declare @t int

declare cur cursor for
(
    select course_id from course
)
open cur
fetch next from cur into @t
while(@@fetch_status=0)
begin
    select a.student_name, b.score, c.course_name from student a, score b, course c 
where  a.student_id = b.student_id AND c.course_id = b.course_id
and b.course_id=@t
and b.score_id in (select top 3 score_id from dbo.score where course_id=@t order by score desc)
and b.score_id not in (select top 2 score_id from dbo.score where course_id=@t order by score desc)
    fetch next from cur into @t
end
close cur
deallocate cur

第一题，你就这样筛选出前15条记录，然后排序就行？如果数据是混乱的了。完全没考虑逻辑。我再加一条数据你就得不到结果。
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) values (19, 6, 3, 100);

如果要考虑分数重复的情况  应该是这样的：


求前五名
select s.student_name , c.course_name , temp.score from student s , course c , (select score.student_id, score.course_id ,score.score , dense_rank() over(partition by course_id  order by score  desc) rn from score) temp where  temp.rn<=5 and s.student_id = temp.student_id and c.course_id = temp.course_id


求第三名
select s.student_name , c.course_name , temp.score from student s , course c , (select score.student_id, score.course_id ,score.score , dense_rank() over(partition by course_id  order by score  desc) rn from score) temp where  temp.rn=3 and s.student_id = temp.student_id and c.course_id = temp.course_id

没必要这么复杂吧

要不要处理相关 重复名次的 ？

确实是这样的，我没考虑到顺序打乱性。还是用分析函数比较妥当

SELECT s.student_name,a.score,c.course_name
FROM score a inner join score b
ON a.course_id=b.course_id inner join student s
ON a.student_id=s.student_id inner join course c
ON a.course_id=c.course_id
WHERE a.score <= b.score
GROUP BY student_name,a.score,course_name
HAVING COUNT(*)<=5
ORDER BY course_name,score

create procedure ScoreTopN
@number int
as
  declare @subjectid int
  declare @sql varchar(500)
 
  declare Subjects cursor local scroll for select id from t_Subjects
  open Subjects
  fetch next from subjects into @subjectid
  set @sql = ''
  while @@FETCH_STATUS = 0
  begin
    set @sql =  @sql + 'select top ' + cast(@number as varchar(10)) + ' StudentID,Score,SubjectID from t_StudentScore where SubjectID=' +
      cast(@subjectid as varchar(10)) + ' union all ' 
    fetch next from subjects into @subjectid
  end
  close Subjects
  deallocate Subjects
  set @sql = LEFT(@sql, Len(@sql) - 10)
  set @sql = 'select name,score,subject from t_students,t_subjects,(' + @sql +
    ')a where t_students.id=a.studentid and t_subjects.id=a.subjectid'
  execute (@sql)