mysql删除重复数据

某表有俩字段a,b,去除掉重复数据.

sql如下,

建表语句

CREATE TABLE temp01(
a VARCHAR(4),
b VARCHAR(4)
);

插入数据

INSERT INTO temp01 VALUES ("lsad","dsaw"),("eadf","2def"),("eadf","2def");

解决方式：

第一步:创建临时表

CREATE TABLE temp02 LIKE temp01;

第二步:查出重复数据保存到临时表

INSERT INTO temp02(SELECT t.a,t.b FROM (SELECT a,b FROM temp01 GROUP BY a,b HAVING COUNT(*)> 1) t);

第三步:删除原表重复数据不做保留

DELETE FROM temp01 WHERE (a,b) IN(
SELECT t.a,t.b FROM (

SELECT a,b FROM temp01 GROUP BY a,b HAVING COUNT(*)> 1

) t
);

第四步:查出临时表数据插入原表

INSERT INTO temp01(SELECT a,b FROM temp02);

这时原表的数据会为一条