我有一个表student(id,name,department,age,score)。我想找到每个部门中拥有最高(年龄最小的学生)分数的最年轻学生。在SQL Server中,我可以使用以下SQL。在Oracle子查询中排序后得到第一行
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中,你不能子句中使用子查询的顺序并没有像关键字没有限制/顶。我必须自己两次加入学生表来查询结果。在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。
2010-08-03
James
+1
在Oracle中,子查询,你可以* *使用ORDER BY子句。 –
2010-08-03 08:09:09
+0
有一个解决方案更简单,没有分析功能,请参阅我的问题的接受答案:http://stackoverflow.com/questions/38180445/oracle-left-join-very-big-table-and-limit-the-joined -row-to-one-the-large –
2016-07-05 05:14:25