Mysql删除重复数据的Sql

执行前

执行Sql

DELETE 
FROM
	test1 
WHERE
	EXISTS (
SELECT
	* 
FROM
	( SELECT max( id ) id, NAME FROM test1 GROUP BY NAME HAVING count( 1 ) > 1 ) T 
WHERE
	test1.NAME = T.NAME 
	AND test1.id < T.id 
	)

执行后

说明：

SELECT max( id ) id, NAME FROM test1 GROUP BY NAME HAVING count( 1 ) > 1

此Sql语句的作用是查询出test1表中根据字段name来分组并且数量大于1的name数据，并且id保留最大的值

SELECT
	* 
FROM
	( SELECT max( id ) id, NAME FROM test1 GROUP BY NAME HAVING count( 1 ) > 1 ) T 
WHERE
	test1.NAME = T.NAME 
	AND test1.id < T.id 
	

where后的条件为查询出name数量大于1并且id非最大的数据

DELETE 
FROM
	test1 
WHERE
	EXISTS (
SELECT
	* 
FROM
	( SELECT max( id ) id, NAME FROM test1 GROUP BY NAME HAVING count( 1 ) > 1 ) T 
WHERE
	test1.NAME = T.NAME 
	AND test1.id < T.id 
	)

delete后面where条件里即为数量大于1并且id非最大的数据，如果满足条件则删除