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 
 )

参考：https://database.51cto.com/art/201011/235159.htm