MySQL 重复数据删除语句

方案 1有自增主键 id推荐最安全假设你的表有主键id没有就用方案 2DELETE a FROM leave_sdxw a JOIN ( -- 找出每组重复数据中 最小的id之外的所有记录 SELECT tenant_id, sddm, fill_vpdm, MIN(id) AS keep_id FROM leave_sdxw WHERE fill_vpdm IS NOT NULL GROUP BY tenant_id, sddm, fill_vpdm HAVING COUNT(*) 1 ) b ON a.tenant_id b.tenant_id AND a.sddm b.sddm AND a.fill_vpdm b.fill_vpdm AND a.id b.keep_id; -- 只删大于最小id的重复数据方案 2没有主键 / 唯一列不推荐但能用-- 先创建临时表存储重复组合 CREATE TEMPORARY TABLE tmp_repeat AS SELECT tenant_id, sddm, fill_vpdm FROM leave_sdxw WHERE fill_vpdm IS NOT NULL GROUP BY tenant_id, sddm, fill_vpdm HAVING COUNT(*) 1; -- 删除重复会每组只留一条取决于数据库存储顺序 DELETE a FROM leave_sdxw a JOIN tmp_repeat b ON a.tenant_id b.tenant_id AND a.sddm b.sddm AND a.fill_vpdm b.fill_vpdm; -- 删除临时表 DROP TEMPORARY TABLE IF EXISTS tmp_repeat;执行前建议先执行查询确认要删的数据不要直接删-- 先查询确认重复数据 SELECT a.* FROM leave_sdxw a JOIN ( SELECT tenant_id, sddm, fill_vpdm, MIN(id) AS keep_id FROM leave_sdxw WHERE fill_vpdm IS NOT NULL GROUP BY tenant_id, sddm, fill_vpdm HAVING COUNT(*) 1 ) b ON a.tenant_id b.tenant_id AND a.sddm b.sddm AND a.fill_vpdm b.fill_vpdm AND a.id b.keep_id;总结优先用方案 1带主键 id安全、高效、不会删光数据执行删除前一定要先查询预览