MySQL根据多个字段删除重复数据(保留一条)
在成千上万条数据中,存在着一些相同的记录,该如何用SQL语句来删除掉重复的呢
话不多说直接上代码

DELETE 
FROM
	表名 
WHERE
	S_ID IN (
	SELECT
		S_ID 
	FROM
		表名 A,
		( SELECT 字段 1, 字段 2 FROM 表名 GROUP BY 字段 1, 字段 2 HAVING COUNT(*) > 1 ) B 
	WHERE
		A.字段 1 = B.字段 1 
		AND A.字段 2 = B.字段 2 
	AND A.S_ID NOT IN ( SELECT MIN( S_ID ) AS ID FROM 表名 GROUP BY 字段 1, 字段 2, 字段 3 HAVING COUNT(*) > 1 ) 
	);

DELETE 
FROM
	student_info 
WHERE
	student_id IN (
	select m.student_id from
	
	(SELECT
		student_id 
	FROM
		student_info A,
		( SELECT student_name,class_name,home_address FROM student_info GROUP BY student_name,class_name,home_address HAVING COUNT(*) > 1 ) B 
	WHERE
		A.student_name  = B.student_name  
		AND A.class_name  = B.class_name 
	  and A.home_address=B.home_address	
	AND A.student_id NOT IN ( SELECT MIN( student_id ) AS ID FROM student_info GROUP BY student_name,class_name,home_address HAVING COUNT(*) > 1 )) m
	)