SQL 在 Oracle 子查询中按顺序获取顶行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3394072/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the top row after order by in Oracle Subquery
提问by James
I have a table student(id, name, department, age, score). I want to find the youngest student who has the highest(among the youngest students) score of each department. In SQL Server, I can use following SQL.
我有一个学生表（id、姓名、部门、年龄、分数）。我想找到每个部门（在最年轻的学生中）得分最高的最年轻的学生。在 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).
However, in Oracle, you cannot use the order by clause in subquery and there is no limit/top like keyword. I have to join the student table with itself two times to query the result. In oracle, I use following SQL.
但是，在 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
Does anyone have any idea to simplify the above SQL for oracle.
有没有人有任何想法为oracle简化上述SQL。
回答by Bharat
try this one
试试这个
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;
回答by SurfingSanta
In addition to Allan's answer, this works fine too:
除了艾伦的回答，这也很好用：
select *
from (SELECT *
FROM student
order by age asc,
score desc)
where rownum = 1;
回答by Allan
In addition to Bharat's answer, it is possible to do this using ORDER BY in the sub-query in Oracle (as point out by Jeffrey Kemp):
除了 Bharat 的回答之外，还可以在 Oracle 的子查询中使用 ORDER BY 来做到这一点（如 Jeffrey Kemp 所指出的）：
SELECT *
FROM student s1
WHERE s1.id IN (SELECT id
FROM (SELECT id, ROWNUM AS rn
FROM student s2
WHERE s1.department = s2.department
ORDER BY age ASC, score DESC)
WHERE rn = 1);
If you use this method, you may be tempted to remove the sub-query and just use rownum = 1. This would result in the incorrect result as the sort would be applied after the criteria (you'd get 1 row that was sorted, not one row from the sorted set).
如果您使用此方法，您可能会想删除子查询并仅使用rownum = 1. 这将导致不正确的结果，因为排序将在条件之后应用（您将得到 1 行已排序，而不是排序集中的一行）。
回答by Diwakar Kumar
select to_char(job_trigger_time,'mm-dd-yyyy') ,job_status from
(select * from kdyer.job_instances ji INNER JOIN kdyer.job_param_values pm
on((ji.job_id = pm.job_id) and (ji.job_spec_id = '10003') and (pm.param_value='21692') )
order by ji.job_trigger_time desc)
where rownum<'2'
