分析函数学习3 ROW_NUMBER

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.

[@more@]

ROW_NUMBER（） OVER( order_by_clause)query_partition_clause

Example
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 Goyal 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 Marvis 203 1
.
.
.
100 Popp 113 6
110 Higgens 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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/219138/viewspace-913422/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/219138/viewspace-913422/