使用 CTE 找到第 n 个最高薪水

Created: November-22, 2018

员工表：

|  ID  | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
| `1`    | Jahangir  | Alam     | Male   | 70000  |
| `2`    | Arifur    | Rahman   | Male   | 60000  |
| `3`    | Oli       | Ahammed  | Male   | 45000  |
| `4`    | Sima      | Sultana  | Female | 70000  |
| `5`    | Sudeepta  | Roy      | Male   | 80000  |
+------+-----------+----------+--------+--------+

CTE（通用表格表达式）：

 WITH RESULT AS
(
    SELECT SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = 1

要找到第二高的薪水，只需将 N 替换为 2.同样，要找到第三高薪，只需将 N 替换为 3。