转载

Oracle提供的序号函数

Oracle提供的序号函数:
以emp表为例:
1: rownum 最简单的序号 但是在order by之前就确定值.
select rownum,t.* from emp t order by ename
  行数 ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 11 7876 ADAMS CLERK 7788 1987-5-23 1100   20
2 2 7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30
3 6 7698 BLAKE MANAGER 7839 1981-5-1 2850   30
4 7 7782 CLARK MANAGER 7839 1981-6-9 2450   10
5 13 7902 FORD ANALYST 7566 1981-12-3 3000   20
6 12 7900 JAMES CLERK 7698 1981-12-3 950   30
7 4 7566 JONES MANAGER 7839 1981-4-2 2975   20
8 9 7839 KING PRESIDENT   1981-11-17 5000   10
9 5 7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30
10 14 7934 MILLER CLERK 7782 1982-1-23 1300   10
11 8 7788 SCOTT ANALYST 7566 1987-4-19 3000   20
12 1 7369 SMITH CLERK 7902 1980-12-17 800   20
13 10 7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30
14 3 7521 WARD SALESMAN 7698 1981-2-22 1250 500 30

2: row_number() over( order by 字段名1,字段名2,...字段名n ) 先排序再确定序号.
select row_number() over( order by ename ) as rm, t.* from emp t
   行数 RM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 1 7876 ADAMS CLERK 7788 1987-5-23 1100   20
2 2 7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30
3 3 7698 BLAKE MANAGER 7839 1981-5-1 2850   30
4 4 7782 CLARK MANAGER 7839 1981-6-9 2450   10
5 5 7902 FORD ANALYST 7566 1981-12-3 3000   20
6 6 7900 JAMES CLERK 7698 1981-12-3 950   30
7 7 7566 JONES MANAGER 7839 1981-4-2 2975   20
8 8 7839 KING PRESIDENT   1981-11-17 5000   10
9 9 7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30
10 10 7934 MILLER CLERK 7782 1982-1-23 1300   10
11 11 7788 SCOTT ANALYST 7566 1987-4-19 3000   20
12 12 7369 SMITH CLERK 7902 1980-12-17 800   20
13 13 7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30
14 14 7521 WARD SALESMAN 7698 1981-2-22 1250 500 30


3: 和上面的不同之处在于PARTITION分区.在每一个小分区内部取序号.
select row_number() over(PARTITION BY deptno order by sal ) as rm, t.* from emp t
   行数 RM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 1 7934 MILLER CLERK 7782 1982-1-23 1300   10
2 2 7782 CLARK MANAGER 7839 1981-6-9 2450   10
3 3 7839 KING PRESIDENT   1981-11-17 5000   10
4 1 7369 SMITH CLERK 7902 1980-12-17 800   20
5 2 7876 ADAMS CLERK 7788 1987-5-23 1100   20
6 3 7566 JONES MANAGER 7839 1981-4-2 2975   20
7 4 7788 SCOTT ANALYST 7566 1987-4-19 3000   20
8 5 7902 FORD ANALYST 7566 1981-12-3 3000   20
9 1 7900 JAMES CLERK 7698 1981-12-3 950   30
10 2 7521 WARD SALESMAN 7698 1981-2-22 1250 500 30
11 3 7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30
12 4 7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30
13 5 7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30
14 6 7698 BLAKE MANAGER 7839 1981-5-1 2850   30


4: rank()在这里表示针对每个部门员工的工资取序号.
select rank() over(PARTITION BY deptno order by sal ) as rnk, t.* from emp t;
   行数 RNK EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 1 7934 MILLER CLERK 7782 1982-1-23 1300   10
2 2 7782 CLARK MANAGER 7839 1981-6-9 2450   10
3 3 7839 KING PRESIDENT   1981-11-17 5000   10
4 1 7369 SMITH CLERK 7902 1980-12-17 800   20
5 2 7876 ADAMS CLERK 7788 1987-5-23 1100   20
6 3 7566 JONES MANAGER 7839 1981-4-2 2975   20
7 4 7788 SCOTT ANALYST 7566 1987-4-19 3000   20
8 4 7902 FORD ANALYST 7566 1981-12-3 3000   20
9 1 7900 JAMES CLERK 7698 1981-12-3 950   30
10 2 7521 WARD SALESMAN 7698 1981-2-22 1250 500 30
11 2 7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30
12 4 7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30
13 5 7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30
14 6 7698 BLAKE MANAGER 7839 1981-5-1 2850   30


5: 从例子中可以看到dense_rank()和rank()的唯一区别就是:
dense_rank()中并列第二名后是第三名.
rank()中并列第二名后是第四名.
select dense_rank() over(PARTITION BY deptno order by sal ) as dense_rnk, t.* from emp t;
   行数 DENSE_RNK EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 1 7934 MILLER CLERK 7782 1982-1-23 1300   10
2 2 7782 CLARK MANAGER 7839 1981-6-9 2450   10
3 3 7839 KING PRESIDENT   1981-11-17 5000   10
4 1 7369 SMITH CLERK 7902 1980-12-17 800   20
5 2 7876 ADAMS CLERK 7788 1987-5-23 1100   20
6 3 7566 JONES MANAGER 7839 1981-4-2 2975   20
7 4 7788 SCOTT ANALYST 7566 1987-4-19 3000   20
8 4 7902 FORD ANALYST 7566 1981-12-3 3000   20
9 1 7900 JAMES CLERK 7698 1981-12-3 950   30
10 2 7521 WARD SALESMAN 7698 1981-2-22 1250 500 30
11 2 7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30
12 3 7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30
13 4 7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30
14 5 7698 BLAKE MANAGER 7839 1981-5-1 2850   30

文章最后发布于: 2011-08-26 10:18:10
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 1024 设计师: 上身试试

分享到微信朋友圈

×

扫一扫,手机浏览