1、 delete from aa a1 where not exists( select * from aa a2 where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age);
2、 delete from aa where id not in(Select min(id) from aa Group by name,age);
3、delete from aa where id in(Select a2.id from aa a1,aa a2 where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age)
2、 delete from aa where id not in(Select min(id) from aa Group by name,age);
3、delete from aa where id in(Select a2.id from aa a1,aa a2 where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age)
效率肯定是不一样的
1的效率一般是最高的,采用NOT EXISTS 内层查询是会走索引的..提示下:用select 1 会稍微好点..
3效率一般,in 会走索引
2的差点 采用NOT IN, 11g以下是走全表查询的,11g 效率应该跟2差不多,好像是会走索引的..但因为内层你用的是分组查询,具体不能和其他两个比,但一般情况我记得,分组是要比连接查询效率低点..
delete from aa a1 where exists( select * from aa a2 where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age);
把id大的数据删掉才是正确的。