mysql 剔除重复记录 保留一条

-- 方法一
/**

错误码: 1093
You can't specify target table 'an' for update in FROM clause
用在select里的表,不能同时被修改
**/
DELETE an  FROM  t_anchor_cp an

WHERE an.c_order IN 
(

	SELECT t0.c_order FROM t_anchor_cp t0,(

	SELECT t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no FROM t_anchor_cp t1 GROUP BY 
	t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no HAVING COUNT(*)>1

	)t2

	WHERE t0.c_anchor_no = t2.c_anchor_no
	AND t0.c_foreign_id = t2.c_foreign_id
	AND t0.c_line_type_id = t2.c_line_type_id
	AND t0.c_order NOT IN 
	(
	SELECT MIN(c_order) FROM t_anchor_cp t1 GROUP BY 
	t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no HAVING COUNT(*)>1

	)

)


-- 方法2 成功执行 使用临时表
DELETE an  FROM  t_anchor_cp an,(

	SELECT t0.c_order FROM t_anchor t0,(

	SELECT t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no FROM t_anchor_cp t1 GROUP BY 
	t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no HAVING COUNT(*)>1

	)t2

	WHERE t0.c_anchor_no = t2.c_anchor_no
	AND t0.c_foreign_id = t2.c_foreign_id
	AND t0.c_line_type_id = t2.c_line_type_id
	AND t0.c_order NOT IN 
	(
	SELECT MIN(c_order) FROM t_anchor_cp t1 GROUP BY 
	t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no HAVING COUNT(*)>1

	)

)tt
WHERE tt.c_order = an.c_order


-- 方法三 创建临时表,用于查询,数据量少的话 
CREATE TABLE t_anchor_cp SELECT * FROM t_anchor