关于用sql求出契合条件的前几条记录(Top X)的两种方法

关于用sql求出符合条件的前几条记录(Top X)的两种方法

如题：现有学生成绩表：    create table t_score(         id                number primary key,         student_name      varchar2(50),         student_gender    char(2),         score             number    );求出男生女生中成绩前五名的记录。方法一(常规sql):     select *       from t_score a      where 5 > (select count(1)                   FROM t_score                  where student_gender = a.student_gender                    AND score > a.score) order by a.student_gender asc, a.score desc;方法二(使用oracle分析函数)：     select *      from (select rank() over(partition by a.student_gender order by a.score desc) rk, a.* from t_score a) t     where t.rk <= 5;