国产精品mp4,美女少妇全过程你懂的久久,日韩一区二区免费在线观看

Oracle提供的序號函數

Posted on 2005-11-12 00:23 Terry的Blog 閱讀(6366) 評論(0) 編輯收藏所屬分類: 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

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: Oracle instant客戶端配置日文字符中常見的亂碼情況---正波浪線“～” 比較Oracle SQL中的IN & EXISTS SQL優化是重點(轉載) PL/SQL支持嵌套事務 SQL and PL/SQL 提交一個查詢有必要用事務嗎?(轉載) Oracle9i的全文檢索技術 Oracle小特性 Oracle SQL依然無可替代--《Mastering Oracle SQL》(轉載)

Terry的Blog

Oracle提供的序號函數

常用鏈接

留言簿(2)

隨筆分類(81)

隨筆檔案(56)

Blog

web sites

其他

軟件下載

積分與排名

最新評論

閱讀排行榜