[A]8i以上版本
select * from (select * from tablename order by sys_guid()) where rownum < N;
select * from (select * from tablename order by dbms_random.value) where rownum< N;
注:dbms_random包需要手工安装,位于$ORACLE_HOME/rdbms/admin/dbmsrand.sql
dbms_random.value(100,200)可以产生100到200范围的随机数
[Q]抽取从N行到M行的记录,如从20行到30行的记录
[A]select * from (select rownum id,t.* from table where ……
and rownum <= 30) where id > 20;
[Q]怎么样实现分组取前N条记录
[A]8i以上版本,利用分析函数
如获取每个部门薪水前三名的员工或每个班成绩前三名的学生。
Select * from
(select depno,ename,sal,row_number() over (partition by depno
order by sal desc) rn
from emp)
where rn<=3
[Q]如何取得一列中第N大的值?
[A]select * from
(select t.*,dense_rank() over (order by t2 desc) rank from t)
where rank = [$N]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/51862/viewspace-180518/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/51862/viewspace-180518/