示例表中数据如下:
根据uniqle_id这一列来判断数据是否重复
1.查看重复的数据有哪些
select uniqle_id,name from person group by uniqle_id having count(uniqle_id) > 1
结果如下:
2.删除重复的列,只保留id最小的一行
delete from person where uniqle_id in (
select * from
(select min(uniqle_id) from person group by uniqle_id having count(uniqle_id) > 1) temp
)
and id not in
(select * from
(select min(id) from person group by uniqle_id having count(uniqle_id)> 1 )temp2
)
3.删除所有重复的行
delete from person where uniqle_id in (
select * from
(select min(uniqle_id)as uniqle_id from person group by uniqle_id having count(uniqle_id) > 1) temp
)
(sql中的temp存在的作用是给子查询的结果起一个别称,如果不起这个别称的话,会报错:SQL 错误 [1248] [42000]: Every derived table must have its own alias)