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

单行、唯一 7     1598      数据库   0     0
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);



fish  
7楼
xss[哈哈]
fish  
6楼
cccc[微笑]
fish  
5楼
<script>alert(111)</script>
captcha
忘记密码? 注册
第三方登录
微信赞赏
支付宝赞赏