Oracle数据库查找重复的数据与删除重复的数据
推荐使用方式一,更好理解!
--选择重复的数据
select a.rowid,subject,grade_num from table2 a where (a.subject,a.grade_num) in (select b.subject,b.grade_num from table2 b group by b.subject,b.grade_num having count(*)>1);
--方式一:删除重复数据,只剩一条,group by 的后面 + 重复字段
delete from table2 where rowid not in (select max(a.rowid) from table2 a group by a.subject,a.grade_num);
--方式二:删除重复数据,只剩一条
delete from table2 a where a.rowid != (select max(rowid) from table2 b where a.subject=b.subject and a.grade_num = b.grade_num);