表中如何进行数据去重?
假定:表A有一个自增字段ID(或随机字段),全表唯一主键,没有实际业务意义;WORKID业务ID,业务属性中唯一,不是表的主键。现出现了业务重复数据,需要去重。
表A中的数据如下所示:
1.1、查看是否有重复数据
select A.WORKID,COUNT(*) from A GROUP BY A.`NAME`,A.WORKID,A.BAK HAVING COUNT(*)>1;
1.2、找出所有重复数据
Select * From A Where A.WORKID In (Select WORKID From A Group By A.`NAME`,A.WORKID,A.BAK Having Count(*)>1)
1.3、找出WORKID重复的数据的最小ID序列:
SELECT WORKID,MIN(ID) minID FROM A GROUP BY WORKID;
1.4、可以保留WORKID的最小ID序列,剩下的删除,下面查询出来的就是重复的数据:
select * from A where A.ID not in(SELECT MIN(ID) minID FROM A GROUP BY WORKID);
1.5、删除掉的就是重复的数据,仅保留一条:
delete from A where A.ID not in(select S.minID from(SELECT MIN(ID) minID FROM A GROUP BY WORKID)S);
1.6、删除所有重复数据:
DELETE From A Where A.WORKID In (Select S.WORKID From (SELECT WORKID FROM A Group By `NAME`,WORKID,BAK Having Count(*)>1)S)
总结:1、还可以根据rowid来控制来进行清理数据的筛选控制;2、数据量大的时候,执行效率需要进行优化。