SQL Oracle 中的第 N 个最高工资
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20091125/
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
Nth max salary in Oracle
提问by Sai
To find out the Nth max sal in oracle i'm using below query
要找出oracle中的第N个最大sal，我正在使用以下查询
SELECT DISTINCE sal
FROM emp a
WHERE (
SELECT COUNT(DISTINCE sal)
FROM emp b
WHERE a.sal<=b.sal)=&n;
But According to me by using the above query it will take more time to execute if table size is big.
i'm trying to use the below query
SELECT sal FROM ( SELECT DISTINCE sal FROM emp ORDER BY sal DESC ) WHERE rownum=3;but not getting output.. any suggetions please .. Please share any link on how to optimise queries and decrease the time for a query to execute.
但是根据我的说法，如果表大小很大，则使用上述查询将需要更多时间来执行。
我正在尝试使用以下查询
SELECT sal FROM ( SELECT DISTINCE sal FROM emp ORDER BY sal DESC ) WHERE rownum=3;但没有得到输出.. 请提出任何建议.. 请分享有关如何优化查询和减少查询执行时间的任何链接。
回答by vhadalgi
try this
尝试这个
select *
from
(
select
sal
,dense_rank() over (order by sal desc) ranking
from table
)
where ranking = 4 -- Replace 4 with any value of N
回答by Kombajn zbo?owy
SELECT sal FROM (
SELECT sal, row_number() OVER (order by sal desc) AS rn FROM emp
)
WHERE rn = 3
Yes, it will take longer to execute if the table is big. But for "N-th row" queries the only way is to look through all the data and sort it. It will be definitely much faster if you have an index on sal.
是的，如果表很大，执行时间会更长。但是对于“第 N 行”查询，唯一的方法是查看所有数据并对其进行排序。如果你有sal的索引，它肯定会快得多。
回答by siva
SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
回答by Hasnain Latif
This will show the 3rd max salary from table employee.
If you want to find out the 5th or 6th (whatever you want) value then just change the where condition like this where rownum<=5" or "where rownum<=6and so on...
这将显示表员工的第三个最高工资。如果您想找出第 5 个或第 6 个（无论您想要什么）值，那么只需更改 where 条件where rownum<=5" or "where rownum<=6，依此类推...
select min(sal) from(select distinct(sal) from emp where rownum<=3 order by sal desc);
回答by Yash
We could write as below mentioned also.
我们也可以写成下面提到的。
select min(sal) from (select sal from emp where rownum=<&n order by sal desc);
select min(sal) from (select sal from emp where rownum=<&n order by sal desc);
回答by Akshay Lokur
Try out following:
请尝试以下操作：
SELECT *
FROM
(SELECT rownum AS rn,
a.*
FROM
(WITH DATA AS -- creating dummy data
( SELECT 'MOHAN' AS NAME, 200 AS SALARY FROM DUAL
UNION ALL
SELECT 'AKSHAY' AS NAME, 500 AS SALARY FROM DUAL
UNION ALL
SELECT 'HARI' AS NAME, 300 AS SALARY FROM DUAL
UNION ALL
SELECT 'RAM' AS NAME, 400 AS SALARY FROM DUAL
)
SELECT D.* FROM DATA D ORDER BY SALARY DESC
) A
)
WHERE rn = 3; -- specify N'th highest here (In this case fetching 3'rd highest)
Cheers!
干杯!
回答by Md Wasi
select * FROM (
select EmployeeID, Salary
, dense_rank() over (order by Salary DESC) ranking
from Employee
)
WHERE ranking = N;
dense_rank() is used for the salary has to be same.So it give the proper output instead of using rank().
Dense_rank() 用于工资必须相同。因此它提供正确的输出而不是使用 rank()。
回答by mahesh
select min(sal) from (select distinct sal from employee order by sal DESC) where rownum<=N;
select min(sal) from (select distinct sal from employee order by sal DESC) where rownum<=N;
place the number whatever the highest sal you want to retrieve.
将数字放在您想要检索的最高 Sal 上。
回答by Abhijeet
SELECT TOP (1) Salary FROM
(
SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC
) AS Emp ORDER BY Salary
This is for 10th max salary, you can replace 10 with n.
这是第 10 个最高工资，您可以用 n 替换 10。
回答by Seth Projnabrata
These queries will also work:
这些查询也将起作用：
Workaround 1)
解决方法 1)
SELECT ename, sal
FROM Emp e1 WHERE n-1 = (SELECT COUNT(DISTINCT sal)
FROM Emp e2 WHERE e2.sal > e1.sal)
Workaround 2) using row_num function.
解决方法 2) 使用 row_num 函数。
SELECT *
FROM (
SELECT e.*, ROW_NUMBER() OVER (ORDER BY sal DESC) rn FROM Emp e
) WHERE rn = n;
Workaround 3 ) using rownum pseudocolumn
解决方法 3 ) 使用 rownum 伪列
Select MAX(SAL)
from (
Select *
from (
Select *
from EMP
order by SAL Desc
) where rownum <= n
)
