比如有一个表,数据量很大,但操作的时候有很多数据重复插入了,现在需要把重复的找出来,然后在重复的数据中只保留一个(不让他重复)
比如表名为student,有很多个字段,比如字段有姓名`name`和身份证号`carNo`,那么我们可以用name和carNo可以联合判断是否重复,语句如下
DELETE FROM student
WHERE (carNo IN
(SELECT tel FROM student GROUP BY carNo
HAVING (COUNT(*) > 1))) AND (`name` NOT IN
(SELECT MIN(`name`) FROM student GROUP BY carNo
HAVING (COUNT(*) > 1)))