DELETE 表名1 FROM
表名1,
(
SELECT
min( id ) id,
stock_organization_id, -- 重复字段1
period_name -- 重复字段2
FROM
表名1
GROUP BY
stock_organization_id,
period_name
HAVING
count( * ) > 1
) t2
WHERE
表名1.stock_organization_id = t2.stock_organization_id
AND 表名1.period_name = t2.period_name
AND 表名1.id > t2.id;
思路:
思路也不难,大概也分为3步来理解:
(SELECT min(id) id, user_id, monetary, consume_time FROM 表名1 GROUP BY stock_organization_id , period_name HAVING count(*) > 1 ) t2 查询出重复记录形成一个集合(临时表t2),集合里是每种重复记录的最小ID
表名1.stock_organization_id = t2.stock_organization_id and 表名1.period_name = t2.period_name 关联 判断重复基准的字段
根据条件,删除原表中id大于t2中id的记录
效率非常高!!!!