删除重复列的SQL语句

一、创建表

 

create table tmp1(id number,name varchar2(50),address varchar2(100));

 

二、插入数据

select * from tmp1 for update --插入数据：

 

SQL> select * from tmp1;
 
        ID NAME                                               ADDRESS
---------- -------------------------------------------------- --------------------------------------------------------------------------------
         1 王敏                                               三门
         2 王敏                                               三门
         3 王敏                                               三门
         4 小丽                                               三门
         5 王敏                                               天台
         6 王利                                               天台
         7 王利                                               天台
         8 王利                                               三门
 
8 rows selected

 

三、创建备份表

create table  tmp2 as select * from tmp1;

select *  from tmp2;
select * from tmp1;

 

四、删除重复数据
delete from tmp1 a
 where (a.name, a.address) in (select name, address
                                 from tmp1
                                group by name, address
                               having count(*) > 1)
   and rowid not in (select min(rowid)
                       from tmp1
                      group by name, address
                     having count(*) > 1);

 

五、核对数据

 

SQL> select * from tmp1;
 
        ID NAME                                               ADDRESS
---------- -------------------------------------------------- --------------------------------------------------------------------------------
         1 王敏                                               三门
         4 小丽                                               三门
         5 王敏                                               天台
         6 王利                                               天台
         8 王利                                               三门
 

 

注：验证成功！