返回各部门工资排名前三位的员工

创建测试用表：

CREATE OR REPLACE VIEW v AS
SELECT '20' AS depno, '101' AS empno, '3000' AS sal FROM DUAL
UNION ALL
SELECT '20' AS depno, '102' AS empno, '3000' AS sal FROM DUAL
UNION ALL
SELECT '20' AS depno, '103' AS empno, '2500' AS sal FROM DUAL
UNION ALL
SELECT '20' AS depno, '104' AS empno, '2000' AS sal FROM DUAL
UNION ALL
SELECT '20' AS depno, '105' AS empno, '1500' AS sal FROM DUAL
UNION ALL
SELECT '30' AS depno, '106' AS empno, '3000' AS sal FROM DUAL
UNION ALL
SELECT '30' AS depno, '107' AS empno, '2500' AS sal FROM DUAL
UNION ALL
SELECT '30' AS depno, '108' AS empno, '2000' AS sal FROM DUAL;
SELECT * FROM v;

SQL代码如下：

SELECT depno,
       empno,
       sal,
       ROW_NUMBER() OVER(PARTITION BY depno ORDER BY sal DESC) AS row_number,
       RANK() OVER(PARTITION BY depno ORDER BY sal DESC) AS rank,
       DENSE_RANK() OVER(PARTITION BY depno ORDER BY sal DESC) AS dense_rank
  FROM v;

执行结果如下：

这里如果用ROW_NUMBER取排名第一的员工，显然会漏掉102这名员工。如果用DENSE_RANK取排名前两位的员工，很明显会返回三条记录。

所以需要具体分析需要，才能决定使用哪一个函数来取前三的员工。

这里选用DENSE_RANK（因需求不定，所以随意选择了一个）取排名前三的员工，SQL代码如下：

SELECT *
  FROM (SELECT depno,
               empno,
               sal,
               DENSE_RANK() OVER(PARTITION BY depno ORDER BY sal DESC) AS dense_rank
          FROM v)
 WHERE dense_rank <= 3;

转载于:https://www.cnblogs.com/minisculestep/p/4894941.html