删除表中重复的记录

 情形一：所有的字段完全重复

         方法一：

                       步骤1：查找出完全重复的记录 select ID from tablename group by ID having count(ID) > 1

                       步骤2：删除表中的ID在以上结果集中 并且 rowid不是最小的那些记录

  delete from ID
where ID in (select ID from people group by ID having count(ID ) > 1)
and ID not in (select min(rowid) from tablename group by ID having count(ID )> 1)

 

       方法二：用distinct过滤掉重复的记录，将结果集保存到临时表里，

                        然后清空表内的内容，最后将临时表的记录写入表

 

 

情形二：部分字段重复

        步骤1：查找出部分字段重复的记录 select username,usertel from tablename group by username,usertel having count(*) > 1

        步骤2：

delete from tablename
where ( username,usertel ) in (select  username,usertel from tablename group by username,usertel having count(*) > 1)
and rowid not in (select min(rowid) from tablename group by  username,usertel having count(*)> 1)

 

 

--以下是删除重复数据的3种方案
--3种方案都是最优化的
--object_name, object_id. 这2个字段用来判断重复

--1.
delete
  from x
where rowid in
        (
        select rd
          from (
                select rowid rd
                    ,row_number() over(partition by object_name, object_id order by rowid) rn
                  from x
               ) x
         where rn > 1
       )

--2.
delete
  from x
where rowid not in (
             select max(rowid)
               from x
              group by object_name, object_id
     )     
    
--3.    
create table tmp_x
AS
    select x1.(字段列表..略)
      from
              (
                select x.*, row_number() over(partition by object_name, object_id order by rowid) rn
                  from x   
              ) x1
     where rn = 1;
    
truncate table x;

insert into x
    select *
      from tmp_x;    

drop table tmp_x;