MySQL数据去重、过滤、转换

去除重复：
DELETE FROM wei_daddy_recommend_copy WHERE id NOT IN (SELECT a.id FROM (SELECT MAX(id) FROM wei_daddy_recommend_copy GROUP BY NAME, sale_price HAVING COUNT(*) >= 1) a);

mysql不允许在查询的数据上进行更新操作，所以在select外面套一层查询

参考：MySQL删除重复记录 https://blog.csdn.net/u010333070/article/details/54910923
过滤、转换数据：
select id, SUBSTRING_INDEX(sale_price,"-",1) as low_price, SUBSTRING_INDEX(sale_price,"-",-1) as high_price from wei_daddy_recommend_copy where id in (select id from wei_daddy_recommend_copy where sale_price like "%-%");
创建表：
create table temp (id int(11) NOT NULL,
        low_price varchar(24), 
        high_price varchar(24),
        primary key (id)
        )
insert into temp select id, LTRIM(SUBSTRING_INDEX(sale_price,"-",1)) as low_price, RTRIM(SUBSTRING_INDEX(sale_price,"-",-1)) as high_price from wei_daddy_recommend_copy where id in (select id from wei_daddy_recommend_copy where sale_price like "%-%");
更新表：
update wei_daddy_recommend_copy a inner join (select id, low_price, high_price from temp) c on a.id = c.id set a.low_price = c.low_price, a.high_price = c.high_price;

引用：mysql中update和select结合使用 https://blog.csdn.net/qq_36823916/article/details/79403696
替换￥:
update wei_daddy_recommend_copy set low_feight = REPLACE(low_feight,"￥","") where low_feight like "￥%";
替换万：
update wei_daddy_recommend_copy set amount = REPLACE(amount,"万","0000") where amount like "%万"