用ROW_NUMBER函数可以解决什么问题

• 作用（Purpose）
  用来统计top-n或者bottom-n
  比如你可以用它来统计（或者说查询）出
  a.各个部门工资最高的前 n个员工
  b.各个部门最先入职的前 n个员工
  所以符合这个应用情景条件是：
  a.1对多（部门对员工）
  b.要对多的一方（员工）最高或最低的某个字段（工资，入职时间等）进行统计
  c.前n个

• 语法（Syntax）
  ROW_NUMBER() over(PARTITION BY **A** ORDER BY **B** desc/asc)
  其中：
  A: 1对多中1的那一方
  B: 最高或最低的某个字段

• 具体实例（Examples）
  各个部门工资最高的前 3个员工

  SELECT department_id, first_name, last_name, salaryFROM(SELECTdepartment_id, first_name, last_name, salary,ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rnFROM HR.employees)WHERE rn <= 3ORDER BY department_id, salary DESC, last_name;

  各个部门最先入职的前 3个员工

  SELECT department_id, first_name, last_name, HIRE_DATE, salary FROM (SELECT department_id, first_name, last_name, HIRE_DATE, salary,   ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE ASC) R     FROM HR.employees)WHERE R <=3ORDER BY department_id, HIRE_DATE ASC, last_name;

  前三个订单总额最高的顾客，每人的订单总额最高前三条记录

  SELECT CUSTOMER_ID, ORDER_TOTALFROM (SELECT CUSTOMER_ID,           ORDER_TOTAL,           ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY ORDER_TOTAL DESC) RN      FROM OE.ORDERS)WHERE RN <= 3AND CUSTOMER_ID IN (SELECT CUSTOMER_ID                     FROM (SELECT CUSTOMER_ID, MAX(ORDER_TOTAL)                             FROM OE.ORDERS                            GROUP BY CUSTOMER_ID                            ORDER BY MAX(ORDER_TOTAL) DESC) T                    WHERE ROWNUM <= 3)ORDER BY CUSTOMER_ID;