oracle分页与去重

分页：rownum

查询emp表中的记录

–分页，每一页显示5条记录
select ename,sal,deptno,rownum from emp where rownum<=5

–查询第二页的数据
select * from (select ename,sal,deptno,rownum rw from emp)
where rw>5 and rw<=10

–查询第二页的数据,并排序
select ename,sal,deptno,rownum
from (select ename,sal,deptno,rownum rw from emp order by sal desc)
where rw>5 and rw<=10

去重：rowid

–将重复记录保留一条

如上，test2表中有4条重复数据，利用rowid可将重复数据删除，步骤如下：

–1.将数据进行分组，按重复信息进行分组
select ename from test2 group by ename
–2.在每一组中选取一条记录
select min(rowid) from test2 group by ename
–3.删除重复的记录
delete from test2 where rowid not in (select min(rowid) from test2 group by ename)