oracle查询重复数据并删除掉多余部分

--查询重复的数据：

select t.stu_name, t.class_name, t.score
  from tb3 t
 where exists (select t1.stu_name
          from tb3 t1
         where t1.stu_name = t.stu_name
         group by t1.stu_name
        having count(t1.stu_name) > 1);

--删除重复数据

delete from tb3 t
 where rowid > (select min(rowid)
                  from tb3 t1
                 where t1.stu_name = t.stu_name
                 group by t1.stu_name
                having count(t1.stu_name) > 1);
commit;

--还有一种方法：

delete from tb2 t
 where t.stu_name in (select stu_name
                        from tb2
                       group by stu_name
                      having count(stu_name) > 1)
   and rowid not in (select min(rowid)
                       from tb2
                      group by stu_name
                     having count(stu_name) > 1);
commit;