rank型
SELECT GZ
FROM (SELECT ROW_NUMBER() OVER() AS RN,
MAX(GZ) OVER(PARTITION BY GZ) AS GZ
FROM EMP)
WHERE RN BETWEEN (SELECT COUNT(1) - 4 FROM EMP) AND
(SELECT COUNT(1) FROM EMP)
dense_rank型
SELECT GZ
FROM EMP
WHERE GZ IN
(SELECT GZ
FROM (SELECT ROW_NUMBER() OVER() AS RN, MAX(GZ) AS GZ
FROM EMP
GROUP BY GZ)
WHERE RN BETWEEN
(SELECT COUNT(1) - 4
FROM (SELECT MAX(GZ) FROM EMP GROUP BY GZ)) AND
(SELECT COUNT(1) FROM (SELECT MAX(GZ) FROM EMP GROUP BY GZ)))
select
sal
,sal_max
,rn
from
(select
sal
,rownum rn
,cal_cnt
,sal_max
from (select
sal
,sum(1) over(partition by 'a') cal_cnt
,max(sal) over(partition by 'a') sal_max
from emp
) bb
)
where rn < cal_cnt - 5
不用order by 用group by 行么,b字段是工资
select *
from temp t1
where t1.b >= (select max(b)
from (select t.b from temp t group by t.b)
where rownum <= (select count(*) from temp) - 4);