Oracle 删除重复数据只留一条

1、查找表中多余的重复记录，重复记录是根据单个字段（字段名）来判断

select * from table_name where 字段名 in (select 字段名 from table_name group by 字段名 having count (字段名) > 1)

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

delete from table_name where 字段名 in (select 字段名 from table_name group by 字段名 having count(字段名) > 1) and rowId not  in (select MIN(rowId)) from  table_name group by 字段名 having count(*) > 1;

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

select * from table_name A where A.字段1, A.字段2 IN ( select B.字段1,B.字段2 from  table_name  B group by B.字段1,B.字段2  having count(*) > 1 )

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

delete from table_name A where A.字段1, A.字段2 IN(select B.字段1, B.字段2 from table_name B group by B.字段1, B.字段2 having count(*) > 1) 

and rowid not  in (select MIN(rowid))  from table_name C group by C.字段1, C.字段2 having count(*) > 1;

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

select * from table_name A where A.字段1, A.字段2 IN(select B.字段1, B.字段2 from table_name B group by B.字段1, B.字段2 having count(*) > 1) 

and rowid not  in (select MIN(rowid))  from table_name C group by C.字段1, C.字段2 having count(*)  > 1;

 

6、HAVING COUNT(*) > 1的用法和理解

作用是保留包含多行的组。

SELECT   class.STUDENT_CODE
FROM   crm_class_schedule class
GROUP BY class.STUDENT_CODE
HAVING   count(*) > 1

执行结果是将[crm_class_schedule]表中[STUDENT_CODE]字段重复的数据显示出来。

像下面的问题就可以直接使用：

问题：查询Table1中num字段有重复的记录。

 

原文链接 : https://www.cnblogs.com/252e/archive/2012/09/13/2682817.html