Mysql 删除重复数据
表结构:
school_id | school_name | total_student | test_takers |
---|---|---|---|
1239 | 河南大学 | 80 | 50 |
1240 | 河南大学 | 60 | 65 |
1241 | 武汉工业大学 | 102 | 58 |
1242 | 四川农业大学 | 98 | 85 |
1243 | 浙江理工大学 | 66 | 70 |
1244 | 浙江理工大学 | 85 | 59 |
注⚠️:新建表格 字段无需上引号 插入语句 字段如果是字符类型记得上引号
新建表:
create table test
(school_id int,school_name varchar(255),total_student int,test_takers int);
插入数据:
insert into test
(school_id,school_name
,total_student,test_takers) values(1239,“河南大学”,80,50),(1240,“河南大学”,60,65),(1241,“武汉工业大学”,102,58),(1242,“四川农业大学”,98,85),(1243,“浙江理工大学”,66,70),(1244,“浙江理工大学”,85,59);
删除所有重复数据:
delete from test where school_name in (select a.school_name from (select school_name from test group by school_name having count(*)>1) a);
查询重复项中最大的id:
select max(school_id) from test group by school_name having count(*)>1;
删除重复数据中id最大的数据:
delete from test where school_id in (select * from (select max(school_id) from test group by school_name having count(*)>1) as ori);
*使用ori(可指定任意名称) 承接查询出的数据作为中间表 否则会查询出错 *
删除除了最大id外的重复数据:
delete from test where school_id not in (select * from (select max(school_id) from test group by school_name having count(*)>1) as ori);
=======================================
出现的错误
1. only_full_group_by :
group by 中必须包含 要查询的字段
2. you can’t specify target table ‘tablename’ for update in from clause
mysql 不能对同一个表同时进行 update(delete) 和 select 联合操作
如果非得要操作 那只能 把 条件中 再关联一次(不能的缘由是因为 条件中的查询是 在 临时表中查的,保存却是原表,解决方法就是将查询和保存都放在临时就可以了)
将SELECT出的结果再通过中间表SELECT一遍,这样就规避了错误。