前言:写之前感觉没那么麻烦啊,研究了好一会才搞出来,记录一下,怕又忘了,刚开始学习,哪里有问题劳烦指出
解决方案
DELETE FROM student WHERE id IN ( SELECT * FROM (SELECT id FROM student GROUP BY id HAVING COUNT(id)>1) )
从最基础开始慢慢写,顺便复习一下知识点
1.先查询一下相同记录
SELECT id FROM student GROUP BY id HAVING COUNT(id)>1
这里用到了聚合函数COUNT(),通常使用聚合函数必须搭配HAVING
将聚合函数理解为条件,HAVING则能够等同于WHERE
2.删除相同记录
DELETE from student where id IN (SELECT id FROM student GROUP BY idHAVING COUNT(id)>1)
id 后面不能跟等号,因为select查出来的数据可能不止一条,需要用 LIKE 或 IN
删除的时候发现这里一直再出问题,报错
> 1093 - You can't specify target table 'student' for update in FROM clause
找了半天也找不出问题
后面上网查到了,这是Oracle的语法,在MYSQL行不通
3.最终解决方案
DELETE FROM student WHERE id IN ( SELECT * FROM (SELECT id FROM student GROUP BY id HAVING COUNT(id)>1) )