【Oracle】 删除重复数据只留一条

查询及删除重复记录的SQL语句
1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断

​select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1);

2、删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录

DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

3、查找表中多余的重复记录（多个字段）

select * from 表 A_ where (A_.Id,A_.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from 表 A_ where (A_.Id,A_.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
 

select * from 表 A_ where (A_.Id,A_.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1);

一：重复数据根据单个字段进行判断

1、首先，查询表中多余的数据，由关键字段（INTERFACE_ID）来查询。

select * from T_INV_CMD_H where INTERFACE_ID in (select INTERFACE_ID from T_INV_CMD_H group by INTERFACE_ID having count(INTERFACE_ID)>1)

2、删除表中重复数据，重复数据是根据单个字段（INTERFACE_ID）来判断，只留有rowid最小的记录

delete from T_INV_CMD_H where (INTERFACE_ID) in 

(select INTERFACE_ID from T_INV_CMD_H group by INTERFACE_ID having count(INTERFACE_ID) >1) 

and rowid not in (select min(rowid) from T_INV_CMD_H group by INTERFACE_ID having count(INTERFACE_ID)>1)

二：重复数据根据多个字段进行判断

1、首先，查询表中重复数据，由关键字段（INTERFACE_ID,CMD_TYPE）来查询。

select * from T_INV_CMD_H H_ where (H_.INTERFACE_ID,H_.CMD_TYPE) in 
(select HI_.INTERFACE_ID,HI_.CMD_TYPE from T_INV_CMD_H HI_ group by  HI_.INTERFACE_ID,HI_.CMD_TYPE  having count(*)>1)

2、删除表中重复数据，重复数据是根据多个字段（INTERFACE_ID,CMD_TYPE）来判断，只留有rowid最小的记录

delete from T_INV_CMD_H H_ where (H_.INTERFACE_ID,H_.CMD_TYPE) in 
(select INTERFACE_ID,CMD_TYPE from T_INV_CMD_H group by INTERFACE_ID,CMD_TYPE having count(*) > 1) 
and rowid not in (select min(rowid) from T_INV_CMD_H group by INTERFACE_ID,CMD_TYPE having count(*)>1)

3、查询表中重复数据，重复数据是根据多个字段（INTERFACE_ID,CMD_TYPE）来判断，不包含rowid最小的记录
 
select INTERFACE_ID,CMD_TYPE from T_INV_CMD_H H_ where (H_.INTERFACE_ID,H_.CMD_TYPE) in 
(select INTERFACE_ID,CMD_TYPE from T_INV_CMD_H group by INTERFACE_ID,CMD_TYPE having count(*) > 1) 
and rowid not in (select min(rowid) from T_INV_CMD_H group by INTERFACE_ID,CMD_TYPE having count(*)>1)