删除oracle表中名称重复的记录

介绍三种方法:

1.建立组合索引

   create index idx_name_id on table(name,id);

   delete from table  a

   where exists(select null from table b

                        where b.name=a.name

                        and b.id>a.id);

2.建立单索引name，用rowid代替id

   delete from table  a

   where exists(select null from table b

                        where b.name=a.name

                        and b.rowid>a.rowid);

3.通过分析函数，根据name分组生成序号，然后删除序号大于1的数据

   delete from table

   where rowid in(select rid

                           from (select rowid as rid,

                                    row_number() over(partition by name order by id)  as seq

                                    from table)

                            where seq>1);