mysql删除重复记录｜mysql删除重复数据

科技智趣坊 7 2050 数据库 1 0 2021-05-13 11:18

mysql删除重复记录｜mysql删除重复数据

删除重复数据，按照多个字段分组的重复

DELETE FROM bank_trans 
WHERE CreateTime>='2021-05-07' 
AND (PrisonID,TransNO,AccountNum,OtherAccountNum) IN 
(SELECT d.PrisonID,d.TransNO,d.AccountNum,d.OtherAccountNum FROM 
(select PrisonID,TransNO,AccountNum,OtherAccountNum from bank_trans WHERE CreateTime>='2021-05-07' GROUP BY PrisonID,TransNO,AccountNum,OtherAccountNum HAVING COUNT(*) > 1)
AS d)
AND ID NOT IN 
(SELECT MIN(n.ID) FROM 
(SELECT ID from bank_trans WHERE CreateTime>='2021-05-07' GROUP BY PrisonID,TransNO,AccountNum,OtherAccountNum HAVING COUNT(*) > 1)
AS n);

查重复：

SELECT * FROM bank_trans 
WHERE CreateTime>='2021-05-07' 
AND (PrisonID,TransNO,AccountNum,OtherAccountNum) IN 
(SELECT d.PrisonID,d.TransNO,d.AccountNum,d.OtherAccountNum FROM 
(select PrisonID,TransNO,AccountNum,OtherAccountNum from bank_trans WHERE CreateTime>='2021-05-07' GROUP BY PrisonID,TransNO,AccountNum,OtherAccountNum HAVING COUNT(*) > 1)
AS d)
AND ID NOT IN 
(SELECT MIN(n.ID) FROM 
(SELECT ID from bank_trans WHERE CreateTime>='2021-05-07' GROUP BY PrisonID,TransNO,AccountNum,OtherAccountNum HAVING COUNT(*) > 1)
AS n);