高手都发愁:这个查询如何做

Question

select * from dinya_test02;select * from 
(
    select 
        t.classes,
        t.name,
        t.score,
        rank() over(partition by t.classes order by t.score desc) rank 
    from 
        dinya_test02 t
) s 
where s.rank<=5;

Answer 1

SQL> select * from dinya_test02;CLASSES                        NAME                                SCORE
------------------------------ ------------------------------ ----------
二                             帅哥1                                  66
二                             帅哥2                                  66
二                             帅哥3                                  77
二                             帅哥4                                  88
二                             帅哥5                                  33
二                             帅哥6                                  26
三                             美女1                                  92
三                             美女2                                  77
四                             孩子1                                  35
四                             孩子2                                  66
四                             孩子3                                  53
一                             学生1                                  70
一                             学生7                                  55
一                             学生2                                  90
一                             学生3                                  77
一                             学生4                                  94
一                             学生5                                  78
一                             学生6                                  9918 rows selectedSQL> select * from
  2  (
  3      select
  4          t.classes,
  5          t.name,
  6          t.score,
  7          rank() over(partition by t.classes order by t.score desc) rank
  8      from
  9          dinya_test02 t
 10  ) s
 11  where s.rank<=5;CLASSES                        NAME                                SCORE       RANK
------------------------------ ------------------------------ ---------- ----------
二                             帅哥4                                  88          1
二                             帅哥3                                  77          2
二                             帅哥1                                  66          3
二                             帅哥2                                  66          3
二                             帅哥5                                  33          5
三                             美女1                                  92          1
三                             美女2                                  77          2
四                             孩子2                                  66          1
四                             孩子3                                  53          2
四                             孩子1                                  35          3
一                             学生6                                  99          1
一                             学生4                                  94          2
一                             学生2                                  90          3
一                             学生5                                  78          4
一                             学生3                                  77          515 rows selectedSQL>