今天心血來潮對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一個的話，就有一個等于數據被刪掉