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


    创建测试用表:

    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;
    
  • 相关阅读:
    GET请求和POST请求的本质区别
    go切片的Add与Del
    滚动到指定位置的问题
    promise---批量调用接口,等待所有的请求发完
    this argument
    html2canvas截图 下载图片
    数组合并去重
    vue项目踩坑
    关于java中的栈和堆
    用python实现一个最简单版本的mysql数据库连接池
  • 原文地址:https://www.cnblogs.com/minisculestep/p/4894941.html
Copyright © 2020-2023  润新知