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--你用数据来试一下就知道了。
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--你用数据来试一下就知道了。
解决方案 »
- oracle后台进程显示
- 为啥分号隔开的两个语句在COMMAND窗口成功,在SQL窗口失败?
- Oracle中日期减小时怎么得到新日期
- Oracle Guid 的问题??
- 怎么批量的把一批记录的ID更新为当前ID的最大值顺序加1呢?
- 高级复制配置完成后在主站点启动复制时报错,高手支援下 ~~
- 我在安装oracle client的管理员安装时 碰到了这样一个问题 谢谢了
- 更新备份LOG表的oracle触发器问题
- windows2003 与oracle10g的问题
- 一条sql语句,以及sqlplus该如何用?急!急!急!!!!!!
- 急:.net连接oracle9的问题
- 我遇到了toad的启动问题,toad一闪就没了
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 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.