今天心血来潮对oracle中的分析函数进行备份
1.row_number
SQL> select ename,sal,deptno,row_number() over(partition by deptno order by sal) c from scott.emp;
ENAME SAL DEPTNO C
---------- --------- ------ ----------
MILLER 1300.00 10 1
CLARK 2450.00 10 2
KING 5000.00 10 3
SMITH 800.00 20 1
ADAMS 1100.00 20 2
JONES 2975.00 20 3
SCOTT 3000.00 20 4
FORD 3000.00 20 5
JAMES 950.00 30 1
MARTIN 1250.00 30 2
WARD 1250.00 30 3
TURNER 1500.00 30 4
ALLEN 1600.00 30 5
BLAKE 2850.00 30 6
以部门id分组，以sal进行排序。每组的序值是不间断，而且是顺序增长
2.rank
SQL> select ename,sal,deptno,rank() over(partition by deptno order by sal) c from scott.emp;
ENAME SAL DEPTNO C
---------- --------- ------ ----------
MILLER 1300.00 10 1
CLARK 2450.00 10 2
KING 5000.00 10 3
SMITH 800.00 20 1
ADAMS 1100.00 20 2
JONES 2975.00 20 3
SCOTT 3000.00 20 4
FORD 3000.00 20 4
JAMES 950.00 30 1
MARTIN 1250.00 30 2
WARD 1250.00 30 2
TURNER 1500.00 30 4
ALLEN 1600.00 30 5
BLAKE 2850.00 30 6
14 rows selected
分析出的结果是跳跃的，如有2个2则下一个是4
3.Dense_rank()
SQL> select ename,sal,deptno,Dense_rank() over(partition by deptno order by sal) c from scott.emp;
ENAME SAL DEPTNO C
---------- --------- ------ ----------
MILLER 1300.00 10 1
CLARK 2450.00 10 2
KING 5000.00 10 3
SMITH 800.00 20 1
ADAMS 1100.00 20 2
JONES 2975.00 20 3
SCOTT 3000.00 20 4
FORD 3000.00 20 4
JAMES 950.00 30 1
MARTIN 1250.00 30 2
WARD 1250.00 30 2
TURNER 1500.00 30 3
ALLEN 1600.00 30 4
BLAKE 2850.00 30 5
14 rows selected
分析出的结时有重复的，与上面的区别是。重复后面的分析结果还是顺序增长
4.删掉数据库中重复的记录
delete from b a where a.rowid <>(select max(b.rowid) from b b where a.a=b.a) ;
思想为rowid是不可能重复的，不等于max一个的话，就有一个等于数据被删掉