MySql-删除重复数据

当想要为表建立唯一索引时,由于表中有重复字段而无法进行时,需要删除重复的数据.

ALTER TABLE t_order_record ADD CONSTRAINT UQ_ORDERNO UNIQUE(orderNo);

查找重复的数据

查找t_order_record表中重复的orderNo的数据.

SELECT * FROM t_order_record WHERE id IN (SELECT id FROM t_order_record GROUP BY orderNo HAVING COUNT(orderNo)>1);

筛选重复数据

查找t_order_record表中重复的orderNo数据,选取重复的数据id最大的那条.

SELECT * FROM t_order_record WHERE id IN (SELECT MAX(id) FROM t_order_record GROUP BY orderNo HAVING COUNT(orderNo)>1);

删除重复数据

直接把SELECT *换成DELETE是不行的,因为查询的结果是储存在临时表中,不能对临时表执行该操作.

DELETE FROM t_order_record WHERE id IN ( SELECT * FROM (SELECT MAX(id) FROM t_order_record GROUP BY orderNo HAVING COUNT(orderNo)>1) AS o);