一 . 分析函数
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