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
Oracle 序号函数
标签:padding smi nal width select 排序 acl table rownum
本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源:http://www.cnblogs.com/luxd/p/7331016.html