SQL 删除重复数据，重复数据只保留ID最小的行

 删除重复数据，重复数据只保留ID最小的行

DELETE
FROM
	t_customer_comment
WHERE
	id IN (
		SELECT
			*
		FROM
			(
				SELECT
					id
				FROM
					t_customer_comment
				WHERE
					remaintain_order_id IN (
						SELECT
							remaintain_order_id
						FROM
							t_customer_comment
						GROUP BY
							remaintain_order_id
						HAVING
							count(remaintain_order_id) > 1
						ORDER BY
							count(remaintain_order_id) DESC
					)
				AND id NOT IN (
					SELECT
						min(id)
					FROM
						t_customer_comment t
					GROUP BY
						remaintain_order_id
					HAVING
						count(remaintain_order_id) > 1
					ORDER BY
						min(id) ASC
				)
			) AS ttt
	)

---

思路：

1：使用 group by ... having 查找出重复的记录 a

2：使用select min(id） group by ...having count(...)>1找出重复数据中id最小的记录 b

3：找出 in a and not in b的数据行，即为需要删除的数据c；

4：直接使用delete from c 在非ORACEL下会报错：[Err] 1093 - You can't specify target table 't_customer_comment' for update in FROM clause；此时需要在c的外边套一层 select * from c as d

5:然后 delete from d ；删除成功！