我如何使用"over(partition by)"获得第二个最高薪水?在Oracle SQL? [英] How I can get Second max salary using "over(partition by)" in oracle SQL?

问题描述

我已经通过执行此查询来获取它:

I already get it by doing this query:

   SELECT * 
    FROM
      (
      SELECT emp_id,salary,row_number() over(order by salary  desc) AS rk 
      FROM test_qaium
      ) 
    where rk=2;

但是我的一个朋友要我从雇员表中查找第二个MAX工资，必须在oracle sql中使用"over(partition by )".有人请帮助我. 并让我明白oracle sql中"Partition by"的概念.

But one of my friend ask me to find second MAX salary from employees table must using "over(partition by )" in oracle sql. Anybody please help me. And clear me the concept of "Partition by" in oracle sql.

推荐答案

Oracle安装程序:

CREATE TABLE test_qaium ( emp_id, salary, department_id ) AS
  SELECT  1, 10000, 1 FROM DUAL UNION ALL
  SELECT  2, 20000, 1 FROM DUAL UNION ALL
  SELECT  3, 30000, 1 FROM DUAL UNION ALL
  SELECT  4, 40000, 1 FROM DUAL UNION ALL -- One highest, one 2nd highest
  SELECT  5, 10000, 2 FROM DUAL UNION ALL
  SELECT  6, 20000, 2 FROM DUAL UNION ALL
  SELECT  7, 30000, 2 FROM DUAL UNION ALL
  SELECT  8, 30000, 2 FROM DUAL UNION ALL -- Two highest, one 2nd highest
  SELECT  9, 10000, 3 FROM DUAL UNION ALL
  SELECT 10, 10000, 3 FROM DUAL UNION ALL -- Two highest, no 2nd highest
  SELECT 11, 10000, 4 FROM DUAL UNION ALL -- One highest, no 2nd highest
  SELECT 12, 20000, 5 FROM DUAL UNION ALL
  SELECT 13, 20000, 5 FROM DUAL UNION ALL
  SELECT 14, 30000, 5 FROM DUAL;          -- One highest, Two 2nd highest

查询:

这将获得每个部门第二高薪的所有行:

This will get all the rows with the 2nd highest salary for each department:

SELECT * 
FROM   (
  SELECT t.*,
         DENSE_RANK() OVER (PARTITION BY department_id
                            ORDER BY salary DESC) AS rnk 
  FROM   test_qaium t
) 
WHERE  rnk=2;

输出:

EMP_ID SALARY DEPARTMENT_ID RNK
------ ------ ------------- ---
     3  30000             1   2
     6  20000             2   2
    12  20000             5   2
    13  20000             5   2

这篇关于我如何使用"over(partition by)"获得第二个最高薪水?在Oracle SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！