高级sql

某学校需要将学生们各科成绩汇总成一个汇总表，学校共有学生50名，要求将每个月的单门考试得分最高的三人的其他科目得分进行汇总，并按总分排序;

 

创建表结构如下：
create table t_student_score
(
 id varchar2(20),--学生ID
 name varchar2(80),--学生姓名
 gender varchar2(2),--学生性别
 subject varchar2(20),--科目
 mnth varchar2(6),--考试月份
 score number(10,2)--考试得分
);

 select t.*
   from (select t.id,
                t.name,
                t.gender,
                t.mnth,
                t.subject,
                t.score,
                tt.totalscore,
                (tt.totalscore - t.score) lasttotal
           from (select t.id, t.name, t.gender, t.mnth, t.subject, t.score
                   from (select t.id,
                                t.name,
                                t.gender,
                                t.mnth,
                                t.subject,
                                t.score,
                                rank() over(partition by t.mnth, t.subject order by t.score desc) rnk
                           from t_student_score t) t
                  where t.rnk < 2) t,
                (select t.id,
                        t.name,
                        t.gender,
                        t.mnth,
                        sum(t.score) totalscore
                   from t_student_score t
                  group by t.id, t.name, t.gender, t.mnth) tt
          where t.id = tt.id
            and t.mnth = tt.mnth) t
  order by t. mnth, t.totalscore