--比如要删除name相同的数据只留下一条
select name from test11 group by name having count(name) >1 --查出重复的记录
select name, min(rowid) id from test11 ;
where name in (select name from test11 group by name having count(name) >1)
group by name; --找出最小的rowid的记录
select * from test11 A ,
(select name, min(rowid) id from test11
where name in (select name from test11 group by name having count(name) >1) group by name) B
where a.name =b.name
and a.rowid >b.id --这样可以找到所有需要删除的重复记录
要一步一步来,最好先查出要删除的数据这样不容易误删数据。对数据库做增删改的时候最好先备份表
备份表数据: create table test11_20180319 as select * from test11 where 1=1;
--删除重复数据
将上段sql select换成delete 但是这样查询的时候做了关联所有这样不能删除掉数据
(SELECT NAME, MIN(ROWID) ID
FROM TEST11
WHERE NAME IN
(SELECT NAME FROM TEST11 GROUP BY NAME HAVING COUNT(NAME) > 1)
GROUP BY NAME) B
WHERE A.NAME = B.NAME
AND A.ROWID > B.ID); --用exists就可以删除了