怎样删除一个表中的重复行？

怎样删除一个表中的重复行？

jettli ( 李杰)      2003-03-14 14:26:32 在  Oracle /  基础和管理 提问

如题

问题点数： 20、回复次数： 6

1楼  hrb_qiuyb   ( 晨钟暮鼓)  回复于  2003-03-14 14:32:02  得分  10

引自www.oradb.net  
  软件环境：    
  1、Windows   NT4.0+ ORACLE   8.0.4  
  2、 ORACLE安装路径为：C:/ORANT  
   
  问题提出：    
  1、当我们想要为一个表创建唯一索引时，如果该表有重复的记录，则无法创建成功。    
  方法原理：    
  1、 Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，  
  　   rowid确定了每条记录是在 ORACLE中的哪一个数据文件、块、行上。  
   
  2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中  
  　   那些具有最大rowid的就可以了，其余全部删除。  
   
  3、以下语句用到了3项技巧：rowid、子查询、别名。  
   
  实现方法：    
  SQL>   create   table   a   (  
      2     bm   char(4), --编码  
      3     mc   varchar2(20) --名称  
      4     )  
      5     /  
   
  表已建立.  
   
  SQL>   insert   into   a   values('1111','1111');  
  SQL>   insert   into   a   values('1112','1111');  
  SQL>   insert   into   a   values('1113','1111');  
  SQL>   insert   into   a   values('1114','1111');  
   
  SQL>   insert   into   a   select   *   from   a;  
   
  插入4个记录.  
   
  SQL>   commit;  
   
  完全提交.  
   
  SQL>   select   rowid,bm,mc   from   a;  
   
  ROWID                             BM       MC  
  ------------------   ----   -------  
  000000D5.0000.0002   1111   1111  
  000000D5.0001.0002   1112   1111  
  000000D5.0002.0002   1113   1111  
  000000D5.0003.0002   1114   1111  
  000000D5.0004.0002   1111   1111  
  000000D5.0005.0002   1112   1111  
  000000D5.0006.0002   1113   1111  
  000000D5.0007.0002   1114   1111  
   
  查询到8记录.  
   
   
  查出重复记录  
  SQL>   select   rowid,bm,mc   from   a   where   a.rowid!=(select   max(rowid)   from   a   b   where   a.bm=b.bm   and   a.mc=b.mc);  
   
  ROWID                             BM       MC  
  ------------------   ----   --------------------  
  000000D5.0000.0002   1111   1111  
  000000D5.0001.0002   1112   1111  
  000000D5.0002.0002   1113   1111  
  000000D5.0003.0002   1114   1111  
   
 

Top

删除重复记录  
  SQL>   delete   from   a   a   where   a.rowid!=(select   max(rowid)   from   a   b   where   a.bm=b.bm   and   a.mc=b.mc);  
   
  删除4个记录.  
   
  SQL>   select   rowid,bm,mc   from   a;  
   
  ROWID                             BM       MC  
  ------------------   ----   --------------------  
  000000D5.0004.0002   1111   1111  
  000000D5.0005.0002   1112   1111  
  000000D5.0006.0002   1113   1111  
  000000D5.0007.0002   1114   1111  
   
 

2楼  playyuer   ( 退休干部 卧鼠藏虫)  回复于  2003-03-14 14:38:41  得分  5

T(f1,f2,f3)  
       
  DELETE   T  
          FROM   T,T   T1  
        WHERE   T.f1=T1.f1    
                    and   T.f2=T1.f2    
                    and   T.f3=T1.f3    
                    and   T.rowid   <   T1.rowid

Top

3楼  shuipipi   ( 水皮皮)  回复于  2003-03-14 15:07:44  得分 0

hrb_qiuyb(大森林)   老师已经讲的淋漓尽致了～  
   
  呵呵～

Top

4楼  dajian2000   ( 咖啡与茶)  回复于  2003-03-14 16:24:17  得分  5

还有个方法：   --这个方法可以一次删除所有重复的纪录。  
  SQL>   select   *   from   a;  
   
                  ID   NAME  
  ----------   --------------------  
                    1   zzz  
                    2   HHH  
                    3   JJJ  
                    1   zzz  
   
  SQL>   select   distinct   *   from   a;  
   
                  ID   NAME  
  ----------   --------------------  
                    1   zzz  
                    2   HHH  
                    3   JJJ  
  SQL>   create   table   zhj   as   select   distinct   *   from   a;  
   
  表已创建。  
  SQL>   drop   table   a;  
   
  表已丢弃。  
  SQL>   create   table   a   as   select   *   from   zhj;  
   
  表已创建。  
  SQL>   select   *   from   a;  
   
                  ID   NAME  
  ----------   --------------------  
                    1   zzz  
                    2   HHH  
                    3   JJJ  
 

Top

5楼  smallcrocodile   ( 大鳄鱼)  回复于  2003-03-14 17:47:19  得分 0

问一下：  
    hrb_qiuyb(大森林)  
   
    rowid   在那个表里

Top

6楼  hai_yu2000   ( 海玉)  回复于  2003-03-14 17:58:18  得分 0

是伪列