RUI个人博客 首页>>Oracle Development>>[原] Oracle row_number() over 函数基本用法

[原] Oracle row_number() over 函数基本用法

row_number() over 函数的基本用法
ROW_NUMBER( ) OVER ([ query_partition_clause ] order_by_clause)

row_number()从1开始,为每一条分组记录返回一个数字。
根据 query_partition_clause 分组,在分组内部根据 order_by_clause 排序 ,而此函数
计算的值就表示每组内部排序后的顺序编号(组内连续的唯一性)。

The following example finds the three highest paid employees in each department in the hr.employees table. Fewer than three rows are returned for departments with fewer than three employees.

SELECT department_id, first_name, last_name, salary, rn
FROM
(
  SELECT
    department_id, first_name, last_name, salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
  FROM employees
)
WHERE rn <= 3
ORDER BY department_id, salary DESC, last_name;
 


昵  称:
邮  箱:
评论内容:
验 证 码:
可用[code][/code]插入代码
点击刷新验证码