主页 > 知识库 > 数据库 > Oracle >

删除表中的重复记录的几种方法

来源:中国IT实验室 作者:佚名 发表于:2012-09-10 14:06  点击:
表记录如下: SQL select * from t1; A B ------ ---------- hh 102 wxc 101 wxc 101 wxc 101 wxc 101 hh 102 hh 102 hh 102 方法一: create table t2 as (select distinct a,b from t1); drop table t1; create table t1 as select * from t2; 方法二:
  表记录如下:
   
    SQL> select * from t1;
   
    A               B
   
    ------ ----------
   
    hh            102
   
    wxc           101
   
    wxc           101
   
    wxc           101
   
    wxc           101
   
    hh            102
   
    hh            102
   
    hh            102
   
    方法一:
   
    create table t2 as (select distinct a,b from t1);
   
    drop table t1;
   
    create table t1 as select * from t2;
   
    方法二:rowid方法
   
    delete from t1 where rowid in (select a.rowid from t1 a,t1 b where a.a = b.a and a.b=b.b and a.rowid > b.rowid);
   
    方法三:max,min函数方法
   
    delete from t1 a where rowid not in(select max(b.rowid) from t1 b where b.a=a.a and b.b=a.b);
   
    delete from t1 a where rowid < (select max(b.rowid) from t1 b where b.a=a.a and b.b=a.b);
   
    delete from t1 a where rowid not in(select min(b.rowid) from t1 b where b.a=a.a and b.b=a.b);
   
    delete from t1 a where rowid > (select min(b.rowid) from t1 b where b.a=a.a and b.b=a.b);
   
    方法四:group by函数方法
   
    delete from t1 where rowid not in(select max(rowid) from t1 group by a,b);
   
    delete from t1 where rowid not in(select min(rowid) from t1 group by a,b);
   
    delete from t1 where (a,b) in (select a,b from t1 group by a,b having(count(*)) > 1) and rowid not in (select max(rowid) from t1 group by a,b having count(*) > 1);
   
    delete from t1 where (a,b) in (select a,b from t1 group by a,b having(count(*)) > 1) and rowid not in (select min(rowid) from t1 group by a,b having count(*) > 1);

    有帮助
    (0)
    0%
    没帮助
    (0)
    0%