示例 1：使用 ROW_NUMBER() 开窗函数

查询员工表中每个部门中前三名工资最高的员工信息

 SELECT DEPARTMENT_NAME,
        LAST_NAME,
        SALARY,
        row_number() OVER (PARTITION BY DEPARTMENT_NAME ORDER BY SALARY DESC) AS RANK
 FROM EMPLOYEES T1,
      DEPARTMENTS T2
 WHERE T1.DEPARTMENT_ID = T2.DEPARTMENT_ID
   AND T2.DEPARTMENT_NAME = 'IT';

示例 2：使用 RANK() 开窗函数

查询员工表中每个部门中工资排名前三的员工信息

 SELECT DEPARTMENT_NAME,
        LAST_NAME,
        SALARY,
        RANK() OVER (PARTITION BY DEPARTMENT_NAME ORDER BY SALARY DESC) AS RANK
 FROM EMPLOYEES T1,
      DEPARTMENTS T2
 WHERE T1.DEPARTMENT_ID = T2.DEPARTMENT_ID
   AND T2.DEPARTMENT_NAME = 'IT';

示例 3：使用 DENSE_RANK() 开窗函数

查询员工表中每个部门中工资相同的员工排名相同，排名前三的员工信息

 SELECT DEPARTMENT_NAME,
        LAST_NAME,
        SALARY,
        DENSE_RANK() OVER (PARTITION BY DEPARTMENT_NAME ORDER BY SALARY DESC) AS RANK
 FROM EMPLOYEES T1,
      DEPARTMENTS T2
 WHERE T1.DEPARTMENT_ID = T2.DEPARTMENT_ID
   AND T2.DEPARTMENT_NAME = 'IT';

示例 4：使用 NTILE() 开窗函数

查询员工表中每个部门中工资分成四组的信息

 SELECT DEPARTMENT_NAME, LAST_NAME, SALARY,
        NTILE(4) OVER (PARTITION BY DEPARTMENT_NAME ORDER BY SALARY DESC) AS GROUP_NUM
 FROM EMPLOYEES T1,
      DEPARTMENTS T2
 WHERE T1.DEPARTMENT_ID = T2.DEPARTMENT_ID
   AND T2.DEPARTMENT_NAME = 'IT';

示例 5：使用 LAG() 开窗函数

查询员工表中每个部门中每个员工的工资和上一个员工工资的差

 SELECT DEPARTMENT_NAME, LAST_NAME, SALARY,
        LAG(SALARY,1) OVER (PARTITION BY DEPARTMENT_NAME ORDER BY LAST_NAME) AS PREV_SALARY,
        SALARY - LAG(SALARY,1) OVER (PARTITION BY DEPARTMENT_NAME ORDER BY LAST_NAME) AS DIFF
 FROM EMPLOYEES T1,
      DEPARTMENTS T2
 WHERE T1.DEPARTMENT_ID = T2.DEPARTMENT_ID
   AND T2.DEPARTMENT_NAME = 'IT';