SQL Server - How to get secondary MAX value by subquery

日前在做一個SQL練習，有提到如何去取出某欄位的值是第二大的所有欄位資料，且如果有

多筆的話也要一起列出 (表示有相等的第二大值)。

SQL語法使用環境為：SQL Server 2008 R2

table資料如下：

從該table看來，Salary第二大的值為50000，因此要如何列出這兩列的資料？

一、考慮如何排除最大的value該欄位

如果你在正常的table下使用MAX(Salary)，勢必只會取得Salary = 80000的資料

SELECT MAX(Salary) FROM Employees

如何排除掉這筆資料？ 可以使用子查詢的觀念

即你的table過濾條件是以另一個table塞選出的data為過濾，如下面的語法，搭配NOT IN使用

，排除最大MAX(Salary)的value

SELECT MAX(Salary) FROM Employees
where
Salary NOT IN(Select MAX(Salary) FROM Employees)

排除掉80000該筆資料，那麼剩下最大的即為50000

二、如何取得所有欄位的資料且Salary相等的也要考慮

由上面可以得知，若在欄位使用了該彙總函數

就無法列出其它的欄位(以上面的語法看來)，只會得到單筆資料及單一一個欄位

這時我又想到子查詢的觀念，若50000已經是我們要的了，那我們就再以該結果為塞選條件

在外層下一個新的SELECT，如下：

SELECT * FROM Employees
WHERE
Salary IN (
 SELECT MAX(Salary) FROM Employees
 where
 Salary NOT IN(Select MAX(Salary) FROM Employees)
)

新的SELECT將列出所有的欄位，條件為該Salary滿足50000的該筆！

如此一來就會得到下面的結果：

感覺就是在做巢狀子查詢一樣XD