MySQL删除多余重复记录
-- 去除重复的报价 -- 项目重复且有plan_id的记录,排序后手动删除 SELECT id,project_id,plan_id FROM business_project_quote WHERE project_id IN ( SELECT project_id FROM business_project_quote WHERE create_user_id IS NULL GROUP BY project_id HAVING count( 1 ) > 1 AND MAX(plan_id) IS NOT NULL ) ORDER BY plan_id ASC -- 项目重复且无plan_id的记录 DELETE FROM business_project_quote WHERE id IN ( SELECT t.id FROM (SELECT id FROM business_project_quote WHERE project_id IN ( SELECT project_id FROM business_project_quote WHERE create_user_id IS NULL GROUP BY project_id HAVING count( 1 ) > 1 ) ORDER BY project_id ASC ) t ) AND id NOT IN ( SELECT dt.id FROM (SELECT MIN( id ) id FROM business_project_quote WHERE project_id IN ( SELECT project_id FROM business_project_quote WHERE create_user_id IS NULL GROUP BY project_id HAVING count( 1 ) > 1 ) GROUP BY project_id ORDER BY project_id ASC ) dt )