数据处理|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 "%万"
推荐阅读
- Docker应用:容器间通信与Mariadb数据库主从复制
- 使用协程爬取网页，计算网页数据大小
- Java|Java基础——数组
- Python数据分析(一)（Matplotlib使用）
- Jsr303做前端数据校验
- Spark|Spark 数据倾斜及其解决方案
- 数据库设计与优化
- 爬虫数据处理HTML转义字符
- 数据库总结语句
- py连接mysql