Oracle学习笔记(1)——查询及删除重复数据

1、查找表中多余的重复记录(根据单个字段studentid)

select * from table_name where studentid in (select studentid from table_name group by studentid having count(studentid) > 1)

2、查找表中多余的重复记录（根据多个字段studentid,name...）

select * from table_name a where (a.studentid,a.name) in(select studentid,name from 表 group by studentid,name having count(*) > 1)

3、删除表中多余的重复记录(根据单个字段studentid)

delete from table_name a where a.studentid in( select studentid from table_name group by studentid having count(studentid) > 1)

4、删除表中多余的重复记录(根据多个字段studentid,name...)

delete from table_name a where (a.studentid,a.name) in( select studentid,name from table_name group by studentid,name having count(*) > 1)

5、删除表中多余的重复记录(根据单个字段studentid)，只保留id最小的记录

delete from table_name a where a.name in( select name from table_name group by id having count(name) > 1)

and a.id not in (select min(id) from table_name group by name having count(*) > 1)

6、删除表中多余的重复记录（根据多个字段name,studentid...），只保留id最小的记录

delete from table_name a where (a.name,a.studentid) in (select name,studentid from table_name group by name,studentid having count(*) > 1)

and a.id not in (select min(id) from table_name group by name,studentid having count(*)>1)

巴特西