Получите верхний ряд после порядка в Подзапросе Oracle
У меня есть студент таблицы (идентификатор, имя, отдел, возраст, счет). Я хочу найти самого молодого студента, у которого есть самое высокое (среди самых молодых студентов) счет каждого отдела. В 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, Вы не можете использовать порядок пунктом в подзапросе и нет никакого предела/вершины как ключевое слово. Я должен присоединиться к студенческой таблице с собой два раза для запросов результата. В оракуле я использую следующий 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
Делает у любого есть любая идея упростить вышеупомянутый SQL для оракула.
2 ответа
попробуйте этот
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;
В дополнение к ответу Бхарата это можно сделать с помощью ORDER BY в подзапросе в Oracle (как указывает Джеффри Кемп):
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);
Если вы воспользуетесь этим методом, у вас может возникнуть соблазн удалить подзапрос и просто используйте rownum = 1 . Это приведет к неверному результату, поскольку сортировка будет применяться после критериев (вы получите 1 отсортированную строку, а не одну строку из отсортированного набора).