（002）每日SQL学习：删除名称重复的数据

create table A
(
  id   VARCHAR2(36),
  name VARCHAR2(100),
  sl   VARCHAR2(36)
);
insert all into a (id,name)values ('1','小吴')
into a (id,name)values('2','小李')

into a (id,name)values('3','小夏')

into a (id,name)values('4','小夏')

into a (id,name)values('5','小明')

into a (id,name)values('6','小明')

select 1 from dual;
--------
注：
oracle中 
无法使用 
insert into a (id,name)values ('1','小吴'),('2','小李'),('3','小夏'),('4','小夏'),('5','小明'),('6','小明');

 

删除名称重复数据：

第一种方法：

delete * from a aa where exists (select  null from a bb where aa.name=bb.name and aa.id>bb.id);

第二种方法：rowid替代id

 

delete * from a aa where exists (select null from a bb where aa.name=bb.name and aa.rowid>bb.rowid)；

 

转载于:https://www.cnblogs.com/littlewu/p/8039377.html