SQL 在 Oracle 数据库中，ROWNUM 和 ROW_NUMBER 有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/174595/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In an Oracle database, what is the difference between ROWNUM and ROW_NUMBER?
提问by Bob Dizzle
What is the difference between ROWNUMand ROW_NUMBER?
ROWNUM和 和有ROW_NUMBER什么区别？
回答by Tony Andrews
ROWNUM is a "pseudocolumn" that assigns a number to each row returned by a query:
ROWNUM 是一个“伪列”，它为查询返回的每一行分配一个数字：
SQL> select rownum, ename, deptno
2 from emp;
ROWNUM ENAME DEPTNO
---------- ---------- ----------
1 SMITH 99
2 ALLEN 30
3 WARD 30
4 JONES 20
5 MARTIN 30
6 BLAKE 30
7 CLARK 10
8 SCOTT 20
9 KING 10
10 TURNER 30
11 FORD 20
12 MILLER 10
ROW_NUMBER is an analytic function that assigns a number to each row according to its ordering within a group of rows:
ROW_NUMBER 是一个分析函数，它根据每行在一组行中的顺序为每一行分配一个数字：
SQL> select ename, deptno, row_number() over (partition by deptno order by ename) rn
2 from emp;
ENAME DEPTNO RN
---------- ---------- ----------
CLARK 10 1
KING 10 2
MILLER 10 3
FORD 20 1
JONES 20 2
SCOTT 20 3
ALLEN 30 1
BLAKE 30 2
MARTIN 30 3
TURNER 30 4
WARD 30 5
SMITH 99 1
回答by Powerlord
From a little reading, ROWNUM is a value automatically assigned by Oracle to a rowset (prior to ORDER BY being evaluated, so don't everORDER BY ROWNUMor use a WHERE ROWNUM < 10with an ORDER BY).
从一个小读数，ROWNUM是Oracle自动分配到一个行集合的值（ORDER之前BY被评估，所以不要过ORDER BY ROWNUM或使用WHERE ROWNUM < 10与ORDER BY）。
ROW_NUMBER()appears to be a function for assigning row numbers to a result set returned by a subquery or partition.
ROW_NUMBER()似乎是一个函数，用于将行号分配给子查询或分区返回的结果集。
回答by Lukas Eder
Apart from the other differences mentioned in answers, you should also consider performance. There is a non-authoritative but very interesting report here, comparing various means of pagination, among which the use of ROWNUMcompared to ROW_NUMBER() OVER():
除了答案中提到的其他差异之外，您还应该考虑性能。这里有一篇非权威但很有趣的报道，比较了各种分页方式，其中ROWNUM对比的使用ROW_NUMBER() OVER()：
回答by darshan kamat
Rownum starts with 1 ..increases after condition evaluated results to true . Hence rownum >=1 returns all rows in table
在条件评估结果为 true 后，Rownum 从 1 ..increases 开始。因此 rownum >=1 返回表中的所有行
回答by Michael OShea
rownumis a pseudocolumn which can be added to any select query, to number the rows returned (starting with 1). They are ordered according to when they were identified as being part of the final result set. (#ref)
rownum是一个伪列，可以添加到任何选择查询中，以对返回的行进行编号（从 1 开始）。它们根据何时被确定为最终结果集的一部分进行排序。( #ref)
row_numberis an analytic's function, which can be used to number the rows returned by the query in an order mandated by the row_number() function.
row_number是一个分析函数，可用于按照 row_number() 函数规定的顺序对查询返回的行进行编号。
