oracle中关于row_number的语法

摘自oracle帮助

PARTITION BY分组，Order by 分组后按照哪种规则排序

• 事例
  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
  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;

  • partition by department_id 对部门分组
  • ORDER BY salary desc 分组后按照职员的薪水降序排列
  • 查询出每个部门薪水最高的前3位雇员