一道面试遇到的数据库题
table test

id name pass
1 aaa 111
2 bbb 222
3 ccc 333

insert into test(name,pass) select name,pass from test

table test

id name pass
1 aaa 111
2 bbb 222
3 ccc 333
4 aaa 111
5 bbb 222
6 ccc 333

去掉重复项

delete from test where id not in(select id from test group by name,pass);

delete from test where id not in (select * from (select * from test group by name,pass) a);

delete from test wehre not exists (select * from test group by name,pass);

三句语句只有第二句在mysql 中调试成功

其他的两条逻辑虽然清楚 但是还要在oracle 中测一下

我通过这个语句得到的提示是

select * from test group by name,pass 这句就可以得到不需要删除的语句

或者说除了这个语句查出来的其他的都是多余的.

测试得到 明显在ORACLE里这种方式行不通

有如下方法在ORACLE里可以实现

delete from test where id not in
(select min(id) from test group by name,pass)

用min();