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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 06:24:25  来源:igfitidea点击:

How to find top three highest salary in emp table in oracle?

sqloracleoracle10g

提问by SunilRai86

How to find top three highest salary in emptable 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;