数据处理|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数据去重、过滤、转换】引用: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 "%万"

    推荐阅读