数据库开发试题总结

STU_ID STU_NAME     STU_AGE  学生表student
---------- -------   ------- 
      1001 张三          23       
      1002 李四          19       
      1003 王五          22       
      1004 赵六          25       
      1005 小王          24       
      1006 小明          22  

-------------------------------
LESSION_ID LESSON_NAME  课程表lession
---------- -----------
         1   语文
         2   数学
         3   英语
         4   物理
         5   化学     
-------------------------------
     STU_ID GRADE LESSION_ID  分数表score
---------- ----- ----------
      1001    98          1
      1002    78          2
      1003    90          4
      1004    89          5
      1005    92          4
      1006    94          5
      1001    57          4
      1004    87          2
      1001    98          2
      1002    78          3
      1003    90          1
      1004    89          2
      1005    92          3
      1006    94          2
      1004    87          1
      1001    98          1
      1002    78          2
      1003    90          4
      1004    89          5
      1005    92          4

如何得到如下格式：
------------------------------------
科目  第一名(姓名+分数) 第二名(姓名+分数) 第三名(姓名+分数)
语文    张三，98
数学    
英语
物理
化学

提供了数据创建码 
create table Lession(
    lession_id number primary key,
    lesson_name varchar2(8));
create table Student (
       stu_id number primary key ,
			 stu_name varchar2(8),
			 stu_age number(4),
			 class_id number(4));
create table Score(
       stu_id number,   grade number(4),	 lession_id number(4));
alter table Score add constraint fk_score_lession foreign key(lession_id)  references Lession(lession_id);
alter table Score add constraint fk_score_student foreign key(stu_id)  references Student(stu_id);

select          LESSON_NAME as '科目',
                   max(case when sequence = 1 then NameGrade else null end) as '第一名(姓名+分数)',
                   max(case when sequence = 2 then NameGrade else null end) as '第二名(姓名+分数)',
                   max(case when sequence = 3 then NameGrade else null end) as '第三名(姓名+分数)'
(
  select LESSON_NAME,STU_NAME+','+convert(varchar,GRADE) as NameGrade,sequence
    (select b.LESSON_NAME,c.STU_NAME,a.GRADE,row_number() over(order by a.GRADE Desc, c.STU_NAME asc) as sequence from score a
    inner join lession b on (a.LESSION_ID = b.LESSION_ID)
    inner join student c on (a.STU_ID  = c.STU_ID) ) d
  where sequence < 4
) e
order by (select LESSION_ID from lession f where f.LESSON_NAME  = e.LESSON_NAME)
order by case(when LESSON_NAME = '语文' then 1,                       when LESSON_NAME = '数学' then 2,                       when LESSON_NAME = '英语' then 3,               when LESSON_NAME = '物理' then 4,               when LESSON_NAME = '化学' then 5,                       else 9999 end)