这是oracle的分页函数
select * from (select name,row_num()over(partition by col1 order by name)as rowcount from a) where rowcount<50;
也可以直接用rownum
select * from (select *,rownum rn from tab where rownum <=50
)where rn >= 20;
使用序列不能基于整个记录集合进行排序,如果指定了order by子句,排序的的是选出来的记录集的排序
select * from (select name,row_num()over(partition by col1 order by name)as rowcount from a) where rowcount<50;
也可以直接用rownum
select * from (select *,rownum rn from tab where rownum <=50
)where rn >= 20;
使用序列不能基于整个记录集合进行排序,如果指定了order by子句,排序的的是选出来的记录集的排序
解决方案 »
- 应用database link 报错 ,那位能点一下原因
- update 问题
- oracle has increased its memory usage over the last x minutes
- 求一句SQL
- 如何调整ORACLE9I的初始化参数?请大吓助我
- OracleOraHome92Agent服务启动不了?
- 启动不了"ManagementServer"服务,提示I/O正在操作中.
- oracle 中如何修改表的字段名(高手指点)
- 初学Oracle,怎么如门啊?各位给点建议!!
- 请大家给4位斑竹提出建议和看法,如何把oarcle版办的更好呢?
- 在线求助,oracle9i 里面抓取一个字串里面某一字段,用substring有用吗?格式怎么样的
- oracle 安装问题!急!
如果你按一个部分进行排序,然后再对部分内的员工进行排序就可以用这样的功能来实现!
Purpose
ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.You cannot use ROW_NUMBER or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.See Also:
"About SQL Expressions" for information on valid forms of expr
Examples
For each department in the sample table oe.employees, the following example assigns numbers to each row in order of employee's hire date:SELECT department_id, last_name, employee_id, ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees;DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
------------- ------------------------- ----------- ----------
10 Whalen 200 1
20 Hartstein 201 1
20 Fay 202 2
30 Raphaely 114 1
30 Khoo 115 2
30 Baida 116 3
30 Tobias 117 4
30 Himuro 118 5
30 Colmenares 119 6
40 Mavris 203 1
.
.
.
100 Popp 113 6
110 Higgins 205 1
110 Gietz 206 2
ROW_NUMBER is a nondeterministic function. However, employee_id is a unique key, so the results of this application of the function are deterministic.