删除表中重复的数据并且保留其中一条-遇到问题

需求：删除表中的重复数据，并且保留重复数据中的一条：

数据表：

 

 

最开始的写法是这样的：

DELETE FROM student WHERE id NOT IN (SELECT MIN(id) FROM student GROUP BY stu_no,stu_name,class_no,class_name,score);

 

但是报错了：[Err] 1093 - You can't specify target table 'student' for update in FROM clause

 

关于这个错误,意思是说你不能先select出同一表中的某些值，再对这个表(在同一语句中)进行其他的操作(update,delete等).Oracle可能不会报错。

 

然后改成：

DELETE FROM student WHERE id NOT IN (SELECT MIN(id) FROM (SELECT * FROM student) GROUP BY stu_no,stu_name,class_no,class_name,score);

 

又报错了：err 1248 - Every derived table must have its own alias

这句话的意思是说每个派生出来的表都必须有一个自己的别名

 

好嘛，再次修改：

DELETE FROM student WHERE id NOT IN (SELECT MIN(id) FROM (SELECT * FROM student) AS t GROUP BY stu_no,stu_name,class_no,class_name,score);

 

ok了