今天心血來潮對(duì)oracle中的分析函數(shù)進(jìn)行備份
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進(jìn)行排序。每組的序值是不間斷，而且是順序增長(zhǎng)
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
分析出的結(jié)果是跳躍的，如有2個(gè)2則下一個(gè)是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
分析出的結(jié)時(shí)有重復(fù)的，與上面的區(qū)別是。重復(fù)后面的分析結(jié)果還是順序增長(zhǎng)
4.刪掉數(shù)據(jù)庫(kù)中重復(fù)的記錄
delete from b a where a.rowid <>(select max(b.rowid) from b b where a.a=b.a) ;
思想為rowid是不可能重復(fù)的，不等于max一個(gè)的話，就有一個(gè)等于數(shù)據(jù)被刪掉