oracle 读取重复记录

查询数据库中agencyflowno重复的纪录

select * from tlotmap where agencyflowno in(
select  agencyflowno from tlotmap group by  agencyflowno having count(agencyflowno)>1)

另外一个例子

select * from taccountdetail where  exists(
select ttransactionid from taccountdetail where userno not like 'p%'   group by ttransactionid having count(ttransactionid)>3 
)

查出重复的记录并只保留一条

delete from student where username in( select   username from student username group by username  having count(username)>1)
and rowid not in( select min(rowid) from student username group by username  having count(username)>1)