一道面试遇到的数据库题
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(); 谢谢
