SQL -- 显示每个部门的第二高工资，如果员工薪水相同，则显示员工工资最低

Question

我需要为每个部门显示第二高的薪水，如果员工薪水相同，则显示工资最少empno。

我有下表如下emp其中deptno 20 人的薪水 3000 在 7788 和 7902 两个empno排名第二。

EMPNO   ENAME   JOB  SAL    DEPTNO
7698    BLAKE   MANAGER  2850   30
7844    TURNER  SALESMAN 1500   30
7499    ALLEN   SALESMAN 1600   30
7654    MARTIN  SALESMAN 1250   30
7521    WARD    SALESMAN 1250   30
7900    JAMES   CLERK    950    30
7788    SCOTT   ANALYST  3000   20
7566    JONES   MANAGER  2975   20
7369    SMITH   CLERK    25000  20
7876    ADAMS   CLERK    1100   20
7902    FORD    ANALYST  3000   20
7839    KING    PRESIDENT 5000  10
7934    MILLER  CLERK    1300   10
7782    CLARK   MANAGER  2450   10

我写了下面的代码：

select * from (
  select e.*, row_number() over (partition by deptno order by sal desc ) rn 
  from emp e  
)where rn = 2;

我得到了下面的结果。但根据我的要求，如果该部门的任何员工工资相同，则应显示员工 ID 最少的工资，但在我的情况下显示 empno 7902。但是我需要用empno 7788显示薪水：

EMPNO   ENAME   JOB       SAL   DEPTNO
7782    CLARK   MANAGER   2450  10
7902    FORD    ANALYST   3000  20
7499    ALLEN   SALESMAN  1600  30

如何实现这一点？

Answer 1

此查询在 MySql 上给出正确的结果

select * from (
    select e.*, row_number() over (partition by deptno order by sal desc, empno asc ) rn 
    from emp e  
) s where rn = 2;

结果

EMPNO   ENAME   JOB         SAL     DEPTNO  rn  
7782    CLARK   MANAGER     2450    10      2  
7788    SCOTT   ANALYST     3000    20      2   
7499    ALLEN   SALESMAN    1600    30      2

使用甲骨文的 dbfiddle

Answer 2

试试这个。

您必须按带有 asc 的子句顺序添加 empno

    select * from ( select e.*, row_number() over (partition by deptno order by 
    sal desc,empno asc ) rn 
    from emp e) where rn = 2;

Answer 3

您可以使用RANK() .RANK() 和 ROW_NUMBER() 之间的区别在于，RANK() 将为相同的数字提供相同的排名，因此您将看到部门 20 的两个2。然后你用另一个 ROW_NUMBER(( 把它包起来，抓住最少的 EMPNO：

SELECT * FROM (
SELECT
  subq.EMPNO,
  subq.DEPTNO,
  ROW_NUMBER() OVER(PARTITION BY subq.DEPTNO ORDER BY EMPNO ASC) AS empno_rownum
FROM (  
  SELECT 
    salary.EMPNO,
    salary.DEPTNO,
    RANK() OVER(PARTITION BY salary.DEPTNO ORDER BY salary.SAL DESC) AS salary_rank
  FROM salary
) AS subq
WHERE subq.salary_rank = 2
) AS subq2
WHERE subq2.empno_rownum = 1

结果如下：

EMPNO   DEPTNO  salary_rank
7782    10  2
7788    20  2
7499    30  2