select * from (select * from table order by col) where rownum<n
不用子查询,想不出更加好的方法,用order by 注定减慢系统性能,或可用group by 替代吧。
group by 不同的字段会有不同的结果。
去年的程序员上有一个关于top_n查询,看一下
试试这种语句,不知道效率怎么样。 select * from (select e_salary, row_number() over ( order by s_id asc ) top from emp ) where top<=n;相关资料: http://www.akadia.com/services/ora_analytic_functions.html#Overview
补充一下,select * from (select * from table order by col) where rownum<n这种语法在oracle8下不能用
select * from (select * from table order by col) where rownum<n就用这种方法吧。
select * from (select * from table order by col) where rownum<n这个方法在 ORACLE8.0版本下会出现错误提示,不能用这种方法,我也不知道要怎么办?
cursor get_data is select * from table order by col;open cursor for i in 1..n loop fetch get_data into v_col1,v_col2..... end loop;n取你需要取的记录数,即循环n次
8.0版的数据库,先建一个带order by .. 的 view 让后再select * from viewname rownum < n
select * from (select * from tablename order by fieldname asc) where rownum<n
select * from (select e_salary, row_number() over ( order by s_id asc ) top from emp ) where top<=n;相关资料:
http://www.akadia.com/services/ora_analytic_functions.html#Overview
ORACLE8.0版本下会出现错误提示,不能用这种方法,我也不知道要怎么办?
for i in 1..n loop
fetch get_data into v_col1,v_col2.....
end loop;n取你需要取的记录数,即循环n次
让后再select * from viewname rownum < n