Oracle 常用排序方法

最新推荐文章于 2021-09-06 21:54:02 发布

原创最新推荐文章于 2021-09-06 21:54:02 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #排序 #排名 #dense_rank

oracle 函数杂谈专栏收录该内容

7 篇文章

订阅专栏

本文通过具体实例演示了如何使用SQL中的排名函数，如row_number()和dense_rank()，来找出每个部门中薪资最高和最低的员工。同时，对比了使用传统子查询方法与窗口函数在执行计划和效率上的差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在我们日常写sql的时候，我们经常遇到一些根据某项排名的情况。今天我就给大家演示几个常用的手段。

SQL> select t.empno,t.ename,t.job,t.hiredate,t.sal,t.deptno   from EMP t order by t.deptno;--大家先看看这次演示的数据

EMPNO ENAME      JOB        HIREDATE    SAL DEPTNO
----- ---------- ---------- --------- ----- ------
 7782 CLARK      MANAGER    09-JUN-81  2450     10
 7839 KING       PRESIDENT  17-NOV-81  5000     10
 7934 MILLER     CLERK      23-JAN-82  1300     10
 7566 JONES      MANAGER    02-APR-81  2975     20
 7902 FORD       ANALYST    03-DEC-81  3000     20
 7876 ADAMS      CLERK      23-MAY-87  1100     20
 7369 SMITH      CLERK      17-DEC-80   800     20
 7788 SCOTT      ANALYST    19-APR-87  3000     20
 7521 WARD       SALESMAN   22-FEB-81  1250     30
 7844 TURNER     SALESMAN   08-SEP-81  1500     30
 7499 ALLEN      SALESMAN   20-FEB-81  1600     30
 7900 JAMES      CLERK      03-DEC-81   950     30
 7698 BLAKE      MANAGER    01-MAY-81  1250     30
 7654 MARTIN     SALESMAN   28-SEP-81  1250     30

14 rows selected.

SQL>
SQL>
SQL>
SQL> SELECT   t.empno,t.ename,t.job,t.hiredate,t.sal,t.deptno  --取出每个部门 sal最小的人
  2    FROM (SELECT emp.*,
  3                 row_number() OVER(PARTITION BY deptno ORDER BY sal) AS  N FROM emp ) t
  4   WHERE N = 1;

EMPNO ENAME      JOB        HIREDATE    SAL DEPTNO
----- ---------- ---------- --------- ----- ------
 7934 MILLER     CLERK      23-JAN-82  1300     10
 7369 SMITH      CLERK      17-DEC-80   800     20
 7900 JAMES      CLERK      03-DEC-81   950     30

SQL>
SQL>
SQL>
SQL> SELECT   t.empno,t.ename,t.job,t.hiredate,t.sal,t.deptno --取出每个部门sal最高的人。大家看上面的基础数据。。部门20有2个最高sal
  2    FROM (SELECT emp.*,
  3                 row_number() OVER(PARTITION BY deptno ORDER BY sal DESC ) AS  N FROM emp ) t
  4  WHERE N = 1;

EMPNO ENAME      JOB        HIREDATE    SAL DEPTNO
----- ---------- ---------- --------- ----- ------
 7839 KING       PRESIDENT  17-NOV-81  5000     10
 7788 SCOTT      ANALYST    19-APR-87  3000     20
 7499 ALLEN      SALESMAN   20-FEB-81  1600     30

SQL>
SQL>
SQL>                                                       
SQL> SELECT   t.empno,t.ename,t.job,t.hiredate,t.sal,t.deptno  --取出每个部门sal最高的人 并排显示
  2     FROM (SELECT emp.*,
  3       dense_rank() OVER(PARTITION BY deptno ORDER BY sal DESC ) AS  N FROM emp ) t
  4  WHERE N = 1;

EMPNO ENAME      JOB        HIREDATE    SAL DEPTNO
----- ---------- ---------- --------- ----- ------
 7839 KING       PRESIDENT  17-NOV-81  5000     10
 7788 SCOTT      ANALYST    19-APR-87  3000     20
 7902 FORD       ANALYST    03-DEC-81  3000     20
 7499 ALLEN      SALESMAN   20-FEB-81  1600     30

我相信大家，就算不要刚才的函数，自己也能用相关的子查询得到上面的结果，但是效率就不会太好看了。看下面的执行计划比较

SQL> SET autotrace ON
SQL> SET linesize 1000
SQL> SET pagesize 2000
SQL> 
SQL> SELECT   t.empno,t.ename,t.job,t.hiredate,t.sal,t.deptno
  2    FROM (SELECT emp.*,
  3                 row_number() OVER(PARTITION BY deptno ORDER BY sal) AS  N FROM emp ) t 
  4  WHERE N = 1;

EMPNO ENAME      JOB        HIREDATE    SAL DEPTNO
----- ---------- ---------- --------- ----- ------
 7934 MILLER     CLERK      23-JAN-82  1300     10
 7369 SMITH      CLERK      17-DEC-80   800     20
 7900 JAMES      CLERK      03-DEC-81   950     30


Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    14 |  1036 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |      |    14 |  1036 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |    14 |   854 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMP  |    14 |   854 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEPTNO" ORDER BY
              "SAL")<=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        996  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>
SQL>  
SQL> WITH t as (SELECT deptno, min(sal) as min_sal FROM emp GROUP BY deptno)  
  2  select emp.empno, emp.ename, emp.deptno, emp.hiredate,emp.sal
  3    from emp, t
  4  where emp.deptno=t.deptno
  5  and emp.sal=t.min_sal;

EMPNO ENAME      DEPTNO HIREDATE    SAL
----- ---------- ------ --------- -----
 7900 JAMES          30 03-DEC-81   950
 7369 SMITH          20 17-DEC-80   800
 7934 MILLER         10 23-JAN-82  1300


Execution Plan
----------------------------------------------------------
Plan hash value: 2230095667

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    14 |  1134 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN           |      |    14 |  1134 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | EMP  |    14 |   770 |     3   (0)| 00:00:01 |
|   3 |   VIEW               |      |    14 |   364 |     4  (25)| 00:00:01 |
|   4 |    HASH GROUP BY     |      |    14 |   364 |     4  (25)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP"."DEPTNO"="T"."DEPTNO" AND "EMP"."SAL"="T"."MIN_SAL")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         21  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
        919  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>