oracle 中的函数

一 . 分析函数



    1.   row_number

       

        用法说明：row_number 为有序组中的每一行返回一个唯一的排序值，序号由ORDER BY 子句指定，从1开始


        语法：  row_number() over ([partition by column] roder_by_clause) 


        [b]举例：scott模式下的emp 表

SQL> select * from emp

 

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------     ---------- ----------         ----------    ----------   ----------
      7369 SMITH                CLERK                    7902 17-12月-80        800                        20
      7499 ALLEN                SALESMAN              7698 20-2月 -81       1600        300         30
      7521 WARD                SALESMAN              7698 22-2月 -81       1250        500         30
      7566 JONES                MANAGER               7839 02-4月 -81       2975                       20
      7654 MARTIN              SALESMAN              7698 28-9月 -81       1250       1400        30
      7698 BLAKE                MANAGER                7839 01-5月 -81       2850                      30
      7782 CLARK                MANAGER                7839 09-6月 -81       2450                      10
      7788 SCOTT                ANALYST                 7566 19-4月 -87       3000                      20
      7839 KING                  PRESIDENT                       17-11月-81       5000                     10
      7844 TURNER              SALESMAN              7698 08-9月 -81       1500          0          30
      7876 ADAMS               CLERK                     7788 23-5月 -87       1100                      20
      7900 JAMES                CLERK                     7698 03-12月-81        950                       30
      7902 FORD                 ANALYST                 7566 03-12月-81       3000                       20
      7934 MILLER               CLERK                    7782 23-1月 -82       1300                      10

     使用函数 row_number 按工资排序编个号：-），看看谁第一。（注：工资相同的排名也不相同）

SQL> select ename,job,deptno,sal,row_number()over (order by sal desc) as sal_rank from emp;

 

ENAME                JOB                    DEPTNO       SAL   SAL_RANK
-------------------- ------------------ ----------   ---------- ----------
KING                 PRESIDENT              10         5000          1
SCOTT                ANALYST                 20         3000          2
FORD                 ANALYST                  20         3000          3
JONES                MANAGER                20         2975         4
BLAKE                MANAGER                30         2850          5
CLARK                MANAGER               10          2450          6
ALLEN                SALESMAN              30         1600          7
TURNER              SALESMAN              30         1500          8
MILLER               CLERK                    10         1300          9
WARD                 SALESMAN              30         1250         10
MARTIN               SALESMAN             30          1250         11
ADAMS                CLERK                    20         1100         12
JAMES                CLERK                     30          950          13
SMITH                CLERK                     20          800          14

   

下面看看在同一个部门下进行比比看谁的工资高：-）。（注：工资相同的排名也不相同）

SQL> select ename,job,deptno,sal,row_number() over (partition by deptno ORDER BY sal desc) ral_rank from emp;

 

ENAME                JOB                    DEPTNO        SAL   RAL_RANK
-------------------- ------------------ ---------- ----------         ----------
KING                 PRESIDENT                10       5000          1
CLARK                MANAGER                  10       2450          2
MILLER               CLERK                      10       1300           3
SCOTT                ANALYST                   20       3000          1
FORD                 ANALYST                    20       3000          2
JONES                MANAGER                  20       2975          3
ADAMS                CLERK                      20       1100          4
SMITH                CLERK                       20        800           5
BLAKE                MANAGER                  30       2850          1
ALLEN                SALESMAN                 30       1600          2
TURNER               SALESMAN                30       1500          3
WARD                 SALESMAN                30       1250           4
MARTIN               SALESMAN                30       1250           5
JAMES                CLERK                       30        950             6

2. rank 



   [b]  用法说明：rank 函数计算一个值在一组值中的排位，排位是以1开头的连续整数。具有相等值的行排位相同，序数随后跳跃相应的数值，即：如果两行的序数为1，则没有序数2，下一行的序号为3.


   语法：rank () over([partition by colunm] order_by_clause)[/b]

    

   由于用法和row_number 想象（“具有相等值的行排位相同，序数随后跳跃相应的数值”就这点不同），这里只简单举例如下：

SQL> select ename,job,deptno,sal,rank () over (partition by deptno order by sal desc) denserank from emp;

 

ENAME                JOB                    DEPTNO        SAL   DENSERANK
-------------------- ------------------ ----------      ----------  ----------
KING                 PRESIDENT              10      5000          1
CLARK                MANAGER                10       2450        2
MILLER               CLERK                   10       1300          3
SCOTT                ANALYST                 20       3000          1
FORD                 ANALYST                  20       3000         1

JONES                MANAGER                20       2975          3
ADAMS                CLERK                     20       1100          4
SMITH                CLERK                      20        800           5
BLAKE                MANAGER                30       2850          1
ALLEN                SALESMAN               30       1600          2
TURNER               SALESMAN              30       1500          3
WARD                 SALESMAN               30       1250          4
MARTIN               SALESMAN               30       1250          4
JAMES                CLERK                       30        950           6