1、Oracle
(1)、row_number
该函数的功能是为查询出来的每一行记录生成一个序号。(如果有相同的也会分出个先后排名)
select dept.dname,ename,sal,
row_number() over( partition by emp.deptno order by sal) rankOrder
from emp
inner join dept on dept.deptno=emp.deptno
where 1=1
order by emp.deptno;
ACCOUNTING MILLER 1300 1
ACCOUNTING CLARK 2450 2
ACCOUNTING KING 5000 3
RESEARCH SMITH 1100 1
RESEARCH ADAMS 1100 2
RESEARCH JONES 2975 3
RESEARCH FORD 3000 4
RESEARCH SCOTT 3000 5
SALES JAMES 950 1
SALES MARTIN 1250 2
SALES WARD 1250 3
SALES TURNER 1500 4
SALES ALLEN 1600 5
SALES BLAKE 2850 6
partition by进行分组,这里按部门编码进行分组,order by进行排序,这里按人员的薪水排序
(2)、rank
rank函数考虑到了over子句中排序字段值相同的情况,也就是说如果有两个第一名,那么后面就直接第三名了:
select dept.dname,ename,sal,
rank() over( partition by emp.deptno order by sal) rankOrder
from emp
inner join dept on dept.deptno=emp.deptno
where 1=1
order by emp.deptno;
ACCOUNTING MILLER 1300 1
ACCOUNTING CLARK 2450 2
ACCOUNTING KING 5000 3
RESEARCH SMITH 1100 1
RESEARCH ADAMS 1100 1
RESEARCH JONES 2975 3
RESEARCH FORD 3000 4
RESEARCH SCOTT 3000 4
SALES JAMES 950 1
SALES MARTIN 1250 2
SALES WARD 1250 2
SALES TURNER 1500 4
SALES ALLEN 1600 5
SALES BLAKE 2850 6
partition by进行分组,这里按部门编码进行分组,order by进行排序,这里按人员的薪水排序(这里RESEARCH部门就出现该情况了)
(3)、dense_rank
dense_rank函数的功能与rank函数类似,只是在生成序号时是连续的,而rank函数生成的序号有可能不连续。(也就是说,如果有两个第一名,下一个还是第二名)
select dept.dname,ename,sal,
dense_rank() over( partition by emp.deptno order by sal) rankOrder
from emp
inner join dept on dept.deptno=emp.deptno
where 1=1
order by emp.deptno;
ACCOUNTING MILLER 1300 1
ACCOUNTING CLARK 2450 2
ACCOUNTING KING 5000 3
RESEARCH SMITH 1100 1
RESEARCH ADAMS 1100 1
RESEARCH JONES 2975 2
RESEARCH FORD 3000 3
RESEARCH SCOTT 3000 3
SALES JAMES 950 1
SALES MARTIN 1250 2
SALES WARD 1250 2
SALES TURNER 1500 3
SALES ALLEN 1600 4
SALES BLAKE 2850 5
partition by进行分组,这里按部门编码进行分组,order by进行排序,这里按人员的薪水排序(这里RESEARCH部门就出现该情况了)
(4)、ntile
ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。
select dept.dname,ename,sal,
ntile(2) over( partition by emp.deptno order by sal) rankOrder
from emp
inner join dept on dept.deptno=emp.deptno
where 1=1
order by emp.deptno;
ACCOUNTING MILLER 1300 1
ACCOUNTING CLARK 2450 1
ACCOUNTING KING 5000 2
RESEARCH SMITH 1100 1
RESEARCH ADAMS 1100 1
RESEARCH JONES 2975 1
RESEARCH FORD 3000 2
RESEARCH SCOTT 3000 2
SALES JAMES 950 1
SALES MARTIN 1250 1
SALES WARD 1250 1
SALES TURNER 1500 2
SALES ALLEN 1600 2
SALES BLAKE 2850 2
partition by进行分组,这里按部门编码进行分组,order by进行排序,这里对数据分为2组
--------------------------------------------------------------------------------------------------------------------------------------
在Oracle中还有一个rownum的东东,也可以产生唯一的序号,常用来进行分页排序,这里不再详细说明