查询以及删除MySQL中重复的数据

先查询重复数据的单号, 再查询重复的数据

select * FROM transaction_detail
WHERE id IN (
	SELECT id 
	FROM (
		SELECT id
		FROM transaction_detail
		GROUP BY trade_flow_no, account_no, service_code, direction, trade_type
		HAVING COUNT(*) > 1
	) dis
);

另一种是使用not in

SELECT * FROM transaction_detail
WHERE id NOT IN (
		SELECT MIN(id)
		FROM transaction_detail
		GROUP BY trade_flow_no, account_no, service_code, direction, trade_type
);

把MIN换成MAX也适用.

 

删除重复的数据:

DELETE FROM transaction_detail
WHERE id NOT IN (
		SELECT id
		FROM (
			SELECT MIN(id)
			FROM transaction_detail
			GROUP BY trade_flow_no, account_no, service_code, direction, trade_type
		) dis
	);

 不能用下面这个,下面这个,会报错

You can't specify target table 'transaction_detail' for update in FROM clause,需要在外面包一层查询.

-- 错误写法
DELETE FROM transaction_detail
WHERE id NOT IN (
		SELECT MIN(id)
		FROM transaction_detail
		GROUP BY trade_flow_no, account_no, service_code, direction, trade_type
);