取(删)重复记录
[@more@]select * from ta t1 where t1.rowid !=(select max(rowid) from ta t2
where t1.id=t2.id and t1.name=t2.name)
如果想删除重复记录，可以把第一个语句的select替换为delete
如果重复的记录的比例不是很大
delete t1 where rowid in (
select rid from
(select rowid rid,row_number() over(partition by mobilephone order by
userid desc) rn from t1)
where rn > 1) ;
删除重复数据的一种高效的方法
---------------------------------
表demo是重复拷贝自dba_objects，有88万左右，不重复的是27323，没有索引
方法一：delete from demo a where a.rowid <> (select max(rowid) from demo b where
b.object_id=a.object_id);
耗时：几个小时以上
方法二： delete from demo where rowid in
(select rid from
(select rowid rid,row_number() over(partition by object_id order by rowid) rn
from demo)
where rn <> 1 );
耗时：30秒
方法三： create table demo2 as
select object_id,owner... from
(select demo.*,row_number() over(partition by object_id order by rowid) rn from demo)
where rn = 1;
truncate table demo; insert into demo select * from demo2; drop table demo2;
共耗时： 10秒，适合大数据量的情况，产生更少回滚量。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30028528/viewspace-1481063/ ，如需转载，请注明出处，否则将追究法律责任。
