ROWNUM Pseudocolumn
来源:互联网 发布:从零开始学mt4编程 编辑:程序博客网 时间:2024/03/29 02:20
For each row returned by a query, the ROWNUM
pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM
of 1, the second has 2, and so on.
You can use ROWNUM
to limit the number of rows returned by a query, as in this example:
SELECT * FROM employees WHERE ROWNUM < 10;
If an ORDER
BY
clause follows ROWNUM
in the same query, then the rows will be reordered by the ORDER
BY
clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER
BY
clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:
SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;
If you embed the ORDER
BY
clause in a subquery and place the ROWNUM
condition in the top-level query, then you can force the ROWNUM
condition to be applied after the ordering of the rows. For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to astop-N reporting:
SELECT * FROM (SELECT * FROM employees ORDER BY employee_id) WHERE ROWNUM < 11;
In the preceding example, the ROWNUM
values are those of the top-level SELECT
statement, so they are generated after the rows have already been ordered byemployee_id
in the subquery.
Conditions testing for ROWNUM
values greater than a positive integer are always false. For example, this query returns no rows:
SELECT * FROM employees WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM
of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM
of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
You can also use ROWNUM
to assign unique values to each row of a table, as in this example:
UPDATE my_table SET column1 = ROWNUM;
Please refer to the function ROW_NUMBER for an alternative method of assigning unique numbers to rows.
Referenced from: http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
- ROWNUM Pseudocolumn
- Rownum
- rownum
- rownum
- rownum
- ROWNUM
- rowNum
- rownum
- rownum
- ROWNUM
- rownum
- @rownum
- 关于rownum
- rownum练习
- 学习rownum
- rownum理解
- oracle(rownum)
- rownum详解
- java加密算法
- 打标、去标工具。
- IOS Label 自适应高度
- 【温故而知新】C和C++篇外篇:COleVariant类型
- c 文件操作函数
- ROWNUM Pseudocolumn
- 实现一屏幕自适应登陆
- (黑马程序员)学习笔记,枚举
- OCP 1Z0 052 53
- mysql常见优化
- 使用do{ } while(0);
- 另辟思路解决 Android 4.0.4 不能监听Home键的问题
- 一群牛人翻译:The Swift Programming Language 中文版
- TCP滑动窗口机制