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

2017-01-02 12:04:52来源:CSDN作者:mozongasl人点击

第七城市

--查询重复的数据:

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;

第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台