常用常新，查找重复记录SQL ，删除其中的一条

 

常用常新，查找重复记录SQL ，删除其中的一条；

select from_accountid,to_account_alias_name from ddm_account_aliasbase limit 1000
--创建唯一记录SQL 
CREATE UNIQUE INDEX ddm_account_aliasbase_unique on ddm_account_aliasbase(from_accountid,to_account_alias_name)
delete  from ddm_account_aliasbase where 
to_account_alias_name='山东立健药店连锁有限公司烟台高新区中海国际社区店'
and modifiedby='杨'
--查找重复记录SQL 
select ddm_account_aliasid,from_accountid,from_accountidname,to_account_alias_name from ddm_account_aliasbase  a
where (a.from_accountid,a.to_account_alias_name) 

in (select from_accountid,to_account_alias_name from ddm_account_aliasbase group by from_accountid,to_account_alias_name having count(*)>1)
and ddm_account_aliasid not in (select max(ddm_account_aliasid) from ddm_account_aliasbase group by from_accountid,to_account_alias_name having count(*)>1)
--删除重复记录SQL 
delete from ddm_account_aliasbase
where (from_accountid,to_account_alias_name) in (select from_accountid,to_account_alias_name
from ddm_account_aliasbase group by from_accountid,to_account_alias_name having count(*)>1)
and ddm_account_aliasid not in (select max(ddm_account_aliasid) from ddm_account_aliasbase group by from_accountid,to_account_alias_name having count(*)>1)

因为创建索引需要，找到其它表的重复记录

create unique index ddm_account_product_uom_index on ddm_account_product_uombase(from_accountid,productid)

delete from ddm_account_product_uombase as a
where (from_accountid,productid) in (select from_accountid,productid from ddm_account_product_uombase group by  from_accountid,productid having count(*)>1) 
and ddm_account_product_uomid not in (
select min(ddm_account_product_uomid) from ddm_account_product_uombase group by  from_accountid,productid)

 

 销售人员与地区-客户对照 表，此表约为500万记录

create index sales_region_accountbase_index  on  sales_region_accountbase(accountid,product_categoryid)

 

 

MySQL根据某一个或者多个字段查找重复数据的sql语句 - dqi1999 - 博客园 (cnblogs.com)