需求:对一张用户表根据name/email/card_num字段去除重复数据;
思路:用group by方法可以查询出'去重'后的数据,将这些数据存储到一张临时表中,然后将临时表的数据存储到指定的表中;
误区及解决方案:group by方法只能获取部分字段(去重指定字段),不能一次获取到完整的数据,但是可以通过max函数获取group by结果集中的id,再根据id集合查询出全部的记录。
SELECT max(id) as id,name,email,card_num FROM users GROUP BY name,email,card_num;
SELECT ID from (SELECT max(id) as id,name,email,card_num FROM users GROUP BY name,email,card_num) as T;
SELECT * from users where id in (SELECT ID from (SELECT max(id) as id,name,email,card_num FROM users GROUP BY name,email,card_num) as T);
create TEMP TABLE tmp_data as SELECT * from users where id in (SELECT ID from (SELECT max(id) as id,name,email,card_num FROM users GROUP BY name,email,card_num) as T);
insert into users_copy1 select * from tmp_data;
select count(*) from users_copy1;
测试结果:1.4w条数据中有2300条数据重复,实际运行结果为0.7s,基本满足现在的需求。
更多MySQL相关技术文章,请访问MySQL教程栏目进行学习!
Copyright © 2019- haog.cn 版权所有 赣ICP备2024042798号-2
违法及侵权请联系:TEL:199 1889 7713 E-MAIL:2724546146@qq.com
本站由北京市万商天勤律师事务所王兴未律师提供法律服务