ROWID面试题-删除表中重复数据(重复数据保留一个)

/*

    ROWID是行ID,通过它一定可以定位到r任意一行的数据记录

   ROWID              DNAME               DEPTNO LOC

    ------------------ ---------------------------- ---------- ----------

   AAASSUAAEAAAAIbAAA ACCOUNTING          10     NEW YORK

   AAASSUAAEAAAAIbAAB RESEARCH            20     DALLAS

   AAASSUAAEAAAAIbAAC SALES               30     CHICAGO

   AAASSUAAEAAAAIbAAD OPERATIONS          40     BOSTON

   AAASSUAAEAAAAIeAAB RESEARCH            20     DALLAS

   AAASSUAAEAAAAIeAAC SALES               30     CHICAGO

   重复数据只是数据内容是重复的,而ROWID是唯一的

*/

/*

    删除表中重复数据：

    分析:1.首先应该查询出所有需要被保留的数据(被保留的数据一定是最早增加的)

           2.再根据(重复数据列)分组查询出各自分组中最小的rowid，只要分组查询的rowid不是在min(rowid)的集合中就全部删除

SQL> select deptno,dname,loc ,min(rowid) from mydept group by deptno,dname,loc;

 DEPTNO DNAME       LOC                MIN(ROWID)

---------- ---------------------------- -------------------------- --------------

 20 RESEARCH        DALLAS             AAASSUAAEAAAAIbAAB

 40 OPERATIONS      BOSTON             AAASSUAAEAAAAIbAAD

 10 ACCOUNTING      NEW YORK           AAASSUAAEAAAAIbAAA

 30 SALES           CHICAGO            AAASSUAAEAAAAIbAAC

SQL> delete from mydept where rowid not in(select min(rowid) from mydept group by deptno, dname,loc);

已删除2行。

  删除了重复数据的表数据如下:

SQL> select * from mydept;

    DEPTNO DNAME                        LOC

---------- ---------------------------- ------------------

        10 ACCOUNTING                   NEW YORK

        20 RESEARCH                     DALLAS

        30 SALES                        CHICAGO

        40 OPERATIONS                   BOSTON

*/