类型：转载 责任编辑：asp.net 日期：2007/05/23
热门软件下载：
在INSERT语句中,下面的语句中什么意思,编号是怎么生成的?
谢谢!
ROW_NUMBER()
OVER(PARTITION BY
id,
name
ORDER BY
date
) AS 编号
网友回答：
id name date 编号
1 你 2004-1-1 1
1 你 2004-1-2 2
1 你 2004-1-8 3
2 你 2004-8-8 1
2 我 2004-5-1 1
2 我 2004-5-2 2
--你用数据来试一下就知道了。
这是Oracle9新增的分析函数.
ROW_NUMBER
Syntax
row_number::=
Text description of row_number
See Also:
"Analytic Functions" for information on syntax, semantics, and restrictions
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 employees 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.
<<这是Oracle9新增的分析函数.>>
--在oracle 9之前的版本就有了. oracle 817 release 1可以用.
表示根据id，name分组，在分组内部根据 date排序，而这个值就表示每组内部排序后的顺序编号