情景是这样的 首先我们业务需要 在一张流水表中需要进行流水的记录 这个记录是从别的平台拉过来的数据 但是他们数据无唯一约束 即流水
这样就很有可能出现单条数据重复的问题
所以这种情况 用join的话 效率可能不是很大 所以就写了以下sql
注意 排重后还需保留一个唯一有效的记录
根据单字段 排重
DELETE FROM `liushui` WHERE `username` IN(
SELECT * FROM(
SELECT `ID` FROM `liushui` WHERE `CODE` IN (
SELECT `username` FROM liushui GROUP BY `username` HAVING COUNT(*)> 1
)
AND `ID` NOT IN(
SELECT MIN(`ID`)FROM liushui GROUP BY `username` HAVING COUNT(*)> 1
)
) T
);
根据多个字段
DELETE FROM `liushui` WHERE `ID` IN(
SELECT * FROM(
SELECT `ID` FROM `liushui` WHERE (`username`,`jointime`) IN (
SELECT `username`,`jointime` FROM `liushui` GROUP BY `username`,`jointime` HAVING COUNT(*)> 1
)
AND `ID` NOT IN(
SELECT MIN(`ID`)FROM `liushui` GROUP BY `username`,`jointime` HAVING COUNT(*)> 1
)
) T
);