sql - 在 Oracle 子查询中获取 order by 后的第一行

Question

我有一个表 student(id, name, department, age, score)。我想找到每个部门得分最高(在最年轻的学生中)的最年轻的学生。在 SQL Server 中，我可以使用以下 SQL。

select * from student s1 
where s1.id in 
(select s2.id from student s2 
where s2.department = s1.department order by age asc, score desc top 1).

但是在Oracle中，子查询中不能使用order by子句，也没有limit/top like关键字。我必须将学生表与自身连接两次才能查询结果。在 Oracle 中，我使用以下 SQL。

select s1.* from student s1, 
(select s2.department, s2.age, max(s2.score) as max_score from student s2, 
(select s3.department, min(s3.age) as min_age from student s3 group by s3.department) tmp1 where 
s2.department = tmp1.department and s2.age = tmp1.min_age group by s2.department, s2.age) tmp2 
where s1.department =tmp2.department and s1.age = tmp2.age and s1.score=tmp2.max_score

有没有人有任何想法为 oracle 简化上述 SQL。

Answer 1

试试这个

select * from
  (SELECT id, name, department, age, score,
  ROW_NUMBER() OVER (partition by department order by age desc, score asc) srlno 
  FROM student) 
where srlno = 1;