介绍三种方法:
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);