mysql删除重复记录|mysql删除重复数据
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>
回复
加载更多