oracle表字段去重

最新推荐文章于 2024-07-16 11:54:08 发布

原创最新推荐文章于 2024-07-16 11:54:08 发布 · 2.9k 阅读

1 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

7 篇文章

订阅专栏

单字段去重

查看重复数据

select * from testrepeat t where t.id in (select id from testrepeat group by id having count(id) > 1);

删除重复记录，保留rowid最小的行

delete from testrepeat t
 where t.id in (select id from testrepeat group by id having count(id) > 1)
   and rowid not in
       (select min(rowid) from testrepeat group by id having count(*) > 1)

多字段去重

查看重复数据

select * from testrepeat t where (t.id,t.phone) in (select id,phone from testrepeat group by id,phone having count(*) > 1);

删除重复记录，保留rowid最小的行

delete from testrepeat t
 where  (t.id,t.phone) in (select id,phone from  testrepeat group by id,phone having count(*) > 1)
   and rowid not in
       (select min(rowid) from testrepeat group by id,phone having count(*) > 1)