MYSQL 删除商品主图重复数据，只保留一条

-- 删除重复数据，只保留一条
当一个数据表中，存在重复的数据，如果重复多条，可以把重复的数据都查出来，然后取最大的ID，根据条件删除ID并不是最大的即可，SQL脚本如下

DELETE
FROM
	t_pd_picture
WHERE
	ID IN (
		SELECT
			ID
		FROM
			(
				SELECT
					ID
				FROM
					t_pd_picture
				WHERE
					product_id IN (
						SELECT
							product_id
						FROM
							`t_pd_picture` t
						WHERE
							t.picture_pd_type = 0
						GROUP BY
							product_id,
							picture_pd_type
						HAVING
							COUNT(product_id) > 1
					)
				AND picture_pd_type = 0
				AND ID NOT IN (
					SELECT
						MIN(ID)
					FROM
						`t_pd_picture` t
					WHERE
						t.picture_pd_type = 0
					GROUP BY
						product_id,
						picture_pd_type
					HAVING
						COUNT(product_id) > 1
				)
			) AS TEMP_TABLE
	);

 

转载于:https://my.oschina.net/u/3155476/blog/3056612