时间戳

首页 > 解决方案 > 我想查询第一高薪和第二高薪之间的差异

sql - 我想查询第一高薪和第二高薪之间的差异

问题描述

我已经拿到了第二高的薪水。

我想显示：员工姓名、工资、部门名称，以及最高和第二高的工资之间的差异。

这是我当前的代码。

select  max(SAL) SecondHighestSalary 
from EMP A, DEPT B
where SAL < (
  select max(A.SAL)
  from EMP A, DEPT B
  where A.DEPTNO = B.DEPTNO and B.LOC = 'New York'
);

谢谢

标签： sqloracle

解决方案

使用DENSE_RANK（如果您想要最高的两个唯一薪水值）或ROW_NUMBER（如果您想要两个最高收入者的薪水，无论关系如何）分析函数，然后使用条件聚合：

SELECT MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
  WHERE  d.loc = 'New York'
);

其中，对于样本数据：

CREATE TABLE emp ( deptno, sal ) AS
SELECT 1, 2000 FROM DUAL UNION ALL
SELECT 1, 2000 FROM DUAL UNION ALL
SELECT 1, 1000 FROM DUAL UNION ALL
SELECT 1,  500 FROM DUAL UNION ALL
SELECT 1,  200 FROM DUAL UNION ALL
SELECT 2, 2000 FROM DUAL UNION ALL
SELECT 2, 1500 FROM DUAL UNION ALL
SELECT 2,  200 FROM DUAL UNION ALL
SELECT 2,  700 FROM DUAL;

CREATE TABLE dept ( deptno, loc ) AS
SELECT 1, 'New York' FROM DUAL UNION ALL
SELECT 2, 'Beijing'  FROM DUAL;

输出：

SAL_DIFFERENCE_IGNORE_TIES | SAL_DIFFERENCE_WITH_TIES
-------------------------: | ----------------------:
                      1000 | 0

或者，如果您想要所有部门：

SELECT deptno,
       MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
)
GROUP BY deptno;SELECT deptno,
       MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
)
GROUP BY deptno;

哪个输出：

部门 | SAL_DIFFERENCE_IGNORE_TIES | SAL_DIFFERENCE_WITH_TIES
-----: | -------------------------: | ----------------------:
     1 | 1000 | 0
     2 | 500 | 500

db<>在这里摆弄

推荐阅读