在 Oracle SQL 中使用"Fetch First"后如何对行进行排序？

Question

我有一个Oracle SQL查询，它使用FETCH FIRST在PeopleSoft数据库中查找年薪最高的员工。

SELECT A.EMPLID, A.ANNUAL_RT
FROM PS_EMPLOYEES A
ORDER BY A.ANNUAL_RT DESC
FETCH FIRST ROW WITH TIES;

EMPLID   ANNUAL_RT
20218    100000
10300    100000
26992    100000
17864    100000

我想按EMPLID对我的结果进行排序。但是，由于我使用的是FETCH FIRST 1 ROW WITH TIES，所以在ORDER BY子句中添加EMPLID会将结果限制为ID号最低的1名员工。

SELECT A.EMPLID, A.ANNUAL_RT
FROM PS_EMPLOYEES A
ORDER BY A.ANNUAL_RT DESC, A.EMPLID ASC
FETCH FIRST 1 ROW WITH TIES;

EMPLID   ANNUAL_RT
10300    100000

如何在不影响FETCH FIRST返回的行的情况下对结果进行排序？

EMPLID   ANNUAL_RT
10300    100000
17864    100000
20218    100000
26992    100000

Answer 1

将查询封装为表表达式(通用表表达式也可以(。这样你就可以进行后期处理。

例如：

select *
from (
SELECT A.EMPLID, A.ANNUAL_RT
FROM PS_EMPLOYEES A
ORDER BY A.ANNUAL_RT DESC
FETCH FIRST 1 ROW WITH TIES
) x
order by emplid;

Answer 2

您可以使用RANK()或DENSE_RANK()而不是FETCH FIRST。

SELECT EMPLID, ANNUAL_RT
FROM (
SELECT
A.EMPLID,
A.ANNUAL_RT,
RANK() OVER (ORDER BY A.ANNUAL_RT DESC) AS RANK_NO
FROM PS_EMPLOYEES A
)
WHERE RANK_NO = 1
ORDER BY EMPLID;

结果：

EMPLID   ANNUAL_RT
10300    100000
17864    100000
20218    100000
26992    100000