sql中查询/删除重复数据

[Albatronhenry]

参考链接
针对没有主键的重复数据

表test20181109
----------------
id    -    name
2     |      c
1     |      a
2     |      b
1     |      a
2     |      b
----------------

查询重复数据：

select id from test20181109 group by id having `count(1)`>1

id
1
2

删除重复数据，并保留其中一条

delete from test20181109 where id in (
select id from test20181109 group by id having count(1)>1
) and rowid not in (
 select min(rowid) test20181109 group by id having count(1)>1
)

处理后的数据：

----------------
id    -    name
2     |      c
1     |      a
----------------