显示EMPLOYEE表中的最高薪资和第二最高薪资

Question

SELECT max(salary),
(SELECT MAX(SALARY) FROM EMPLOYEE
WHERE SALARY NOT IN(SELECT MAX(SALARY) FROM EMPLOYEE)) as 2ND_MAX_SALARY;

这给了我一个错误: FROM关键字未在期望的位置找到

Answer 1

您希望表的前2列按其中一列排序(Oracle12c R1中提供了FETCH NEXT子句)

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 2
FETCH NEXT 2 ROWS ONLY;

使用

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 2
FETCH NEXT 2 ROWS WITH TIES;

如果您想返回所有具有第一或第二最高工资的员工:公司中可能只有一个最高工资金额，但有多个员工获得该金额。这些行就是平局。

Answer 2

如果您使用低于12c Oracle数据库版本，rank分析函数可能会有所帮助。

对于样本行：

SQL> select * from employee order by salary desc;

ENAME          SALARY
---------- ----------
KING             5000     --> highest salary
FORD             3000     --> Ford and Scott "share" the 2nd
SCOTT            3000     --> highest salary
JONES            2975
BLAKE            2850
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300
WARD             1250
MARTIN           1250
ADAMS            1100
JAMES             950
SMITH             800

14 rows selected.

在子查询(或CTE，就像我所做的那样)中，计算每个工资的排名，然后在主查询中，选择按最高工资排名的行：

SQL> with temp as
  2    (select ename,
  3            salary,
  4            rank() over (order by salary desc) rnk
  5     from employee
  6    )
  7  select ename, salary
  8  from temp
  9  where rnk <= 2
 10  order by rnk desc;

ENAME          SALARY
---------- ----------
SCOTT            3000
FORD             3000
KING             5000

SQL>

Answer 3

SELECT MAX(salary) AS max_salary, 
      (SELECT MAX(salary) 
       FROM employee 
       WHERE salary NOT IN (SELECT MAX(salary) 
                            FROM employee
                           )
      ) AS 2nD_max_salary 
FROM employee;