SQL 如何在oracle的emp表中找到前三名最高工资?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2943429/
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
How to find top three highest salary in emp table in oracle?
提问by SunilRai86
How to find top three highest salary in emp
table in oracle?
如何emp
在oracle的表格中找到前三名最高工资?
回答by Aamir
SELECT *FROM
(
SELECT *FROM emp
ORDER BY Salary desc
)
WHERE rownum <= 3
ORDER BY Salary ;
回答by Bharat
You can try.
你可以试试。
SELECT * FROM
(
SELECT EMPLOYEE, LAST_NAME, SALARY,
RANK() OVER (ORDER BY SALARY DESC) EMPRANK
FROM emp
)
WHERE emprank <= 3;
This will give correct output even if there are two employees with same maximun salary
即使有两个员工的最高工资相同,这也会给出正确的输出
回答by Martin Smith
Something like the following should do it.
像下面这样的事情应该做。
SELECT Name, Salary
FROM
(
SELECT Name, Salary
FROM emp
ORDER BY Salary desc
)
WHERE rownum <= 3
ORDER BY Salary ;
回答by Avadhoot Khadilkar
SELECT a.ename, b.sal
FROM emp a, emp b
WHERE a.empno = b.empno
AND
3 > (SELECT count(*) FROM emp b
WHERE a.sal = b.sal);
Without using TOP, ROWID, rank etc. Works with oldest sql also
不使用 TOP、ROWID、rank 等。 也适用于最旧的 sql
回答by zabi
Select ename, job, sal from emp
where sal >=(select max(sal) from emp
where sal < (select max(sal) from emp
where sal < (select max(sal) from emp)))
order by sal;
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
FORD ANALYST 3000
SCOTT ANALYST 3000
回答by BHANU
SELECT * FROM
(
SELECT EMPLOYEE, LAST_NAME, SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) EMPRANK
FROM emp
)
WHERE emprank <= 3;
回答by Lukasz Szozda
You could use DBMS_STAT_FUNCS.Summary
:
你可以使用DBMS_STAT_FUNCS.Summary
:
SET SERVEROUTPUT ON;
DECLARE
s DBMS_STAT_FUNCS.SummaryType;
BEGIN
DBMS_STAT_FUNCS.SUMMARY('HR', 'EMPLOYEES', 'SALARY',3, s);
DBMS_OUTPUT.put_line('Top 3: ' || s.top_5_values(1) || '-'
|| s.top_5_values(2) || '-' || s.top_5_values(3));
END;
/
Output:
输出:
Top 3: 24000-17000-17000
回答by Paras
SELECT Name, Salary
FROM
(
SELECT Name, Salary
FROM emp
ORDER BY Salary desc
)
WHERE rownum <= 3
ORDER BY Salary ;
回答by Naweez
select top(3) min(Name),TotalSalary,ROW_NUMBER() OVER (Order by TotalSalary desc) AS RowNumber FROM tbl_EmployeeProfile group by TotalSalary
select top(3) min(Name),TotalSalary,ROW_NUMBER() OVER (Order by TotalSalary desc) AS RowNumber FROM tbl_EmployeeProfile group by TotalSalary
回答by Rohan Khude
Limit The Query To Display Only The Top 3 Highest Paid Employees. : Query ? Oracle PL / SQL
将查询限制为仅显示收入最高的前 3 名员工。: 询问 ?甲骨文PL/SQL
create table employee(
emp_no integer primary key
,lastname varchar2(20) not null
,salary number(3)
);
insert into employee(emp_no,lastname,salary)
values(1,'Tomy',2);
insert into employee(emp_no,lastname,salary)
values(2,'Hymany',3);
insert into employee(emp_no,lastname,salary)
values(3,'Joey',4);
insert into employee(emp_no,lastname,salary)
values(4,'Janey',5);
select lastname, salary
from (SELECT lastname, salary FROM employee ORDER BY salary DESC)
where rownum <= 3 ;
OUTPUT
输出
LASTNAME SALARY
-------------------- ----------
Janey 5
Joey 4
Hymany 3
drop table employee;