实现重复数据删除SQL

》》情景：库表中有多条重复数据，请用SQL实现删除一条保留一条。

--创建表SQL
create table TEMP (
ID number,--主键
name varchar2(50),
insert_date date
);

》》实现方式：

--查询出所有有重复的数据SQL
select ID,name,count(ID)
from TEMP
group by name,ID
having count(ID)>1
order by ID ;

若 主键不重复

--删除重复的数据，保留最小ID值的SQL
delete from TEMP where ID not in (
select min(ID)
from TEMP
group by name
);
--扩展
delete from TEMP where name in
( select name from TEMP GROUP BY name having count(name) > 1)
and ID not in (
select min(ID)
from TEMP
groupby name
);

若主键也重复

--删除重复的数据，保留最小ID值的SQL
delete from TEMP where name in(
selecct name from temp group by name having count(name) > 1
) and rowid not in (
select min(rowid)
from TEMP
group by name
having count(name) > 1
);

补充：ROWID：数据库中行的全局唯一地址,通常来说，一个rowid值唯一标识数据中的一行.