我利用rowid删除表t中的重复记录,比如我认为a,b相同就是重复的,需要删除之后只保留一条。
一开始这样写:
delete from t where rowid not in (select rowid from (select distinct a, b from t));
结果不行。后来写成这样:delete from t where rowid not in (select max(rowid) from t group by a, b);
就OK了。有没有哪位知道第一种写法为啥无效?
一开始这样写:
delete from t where rowid not in (select rowid from (select distinct a, b from t));
结果不行。后来写成这样:delete from t where rowid not in (select max(rowid) from t group by a, b);
就OK了。有没有哪位知道第一种写法为啥无效?
select rowid from (select distinct a, b from t)
(select distinct a, b from t) 不是一张确实的表,Oracle是无法辨认他的ROWID的
rowid不是你想有它就有的啊,它是oracle给数据库表绑定的定向,可不是给任意的集合的。