/*rank(),row_number(),dense_rank()的区别*/
rank() 结果中会有重复序列,并且会出现跳号
select ename,sal,rank()over(order by sal) from emp
ENAME SAL RANK()OVER(ORDERBYSAL)
---------- ---------- ----------------------
SCOTT 3000 12
FORD 3000 12
KING 5000 14
dense_rank 结果中会出现重复序列 但不会出现跳号现象
select ename,sal,dense_rank()over(order by sal) from scott.emp;
ENAME SAL DENSE_RANK()OVER(ORDERBYSAL)
---------- ---------- ----------------------------
SCOTT 3000 11
FORD 3000 11
KING 5000 12
row_number()结果不会有重复序列,重复值随机排列,不会出现跳号
select ename,sal,row_number()over(order by sal) from scott.emp;
ENAME SAL ROW_NUMBER()OVER(ORDERBYSAL)
---------- ---------- ----------------------------
SCOTT 3000 12
FORD 3000 13
KING 5000 14
rank() 结果中会有重复序列,并且会出现跳号
select ename,sal,rank()over(order by sal) from emp
ENAME SAL RANK()OVER(ORDERBYSAL)
---------- ---------- ----------------------
SCOTT 3000 12
FORD 3000 12
KING 5000 14
dense_rank 结果中会出现重复序列 但不会出现跳号现象
select ename,sal,dense_rank()over(order by sal) from scott.emp;
ENAME SAL DENSE_RANK()OVER(ORDERBYSAL)
---------- ---------- ----------------------------
SCOTT 3000 11
FORD 3000 11
KING 5000 12
row_number()结果不会有重复序列,重复值随机排列,不会出现跳号
select ename,sal,row_number()over(order by sal) from scott.emp;
ENAME SAL ROW_NUMBER()OVER(ORDERBYSAL)
---------- ---------- ----------------------------
SCOTT 3000 12
FORD 3000 13
KING 5000 14
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28719055/viewspace-1800172/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28719055/viewspace-1800172/