星期五, 12月 16, 2011

[Challenge] 找出各部門第二高薪人員

Beyond Relational TSQL Beginner's Challenge 1,個人是利用次序函數 RANK() 來做到,次序函數是從 SQL Server 2005 才開始有,在眾多的解法中,有找到非次序函數的解法,可以參考看看。^_^
  • 需求:找出各部門第二高薪人員,而且假如第二高薪有多個人員,也必須一併列出來。
  • 資料來源(題目內有資料來源的 script)
EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
1           T Cook          Finance         40000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
4           D Adams         Finance         15000.00
5           M Williams      IT              80000.00
6           D Jones         IT              40000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00
9           A Anderson      Back-Office     25000.00
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
12          T Clerk         Back-Office     10000.00
  • 利用次序函數 RANK()
SELECT T.EmployeeID , T.EmployeeName, T.Department, T.Salary
FROM
  (
    SELECT EmployeeID , EmployeeName, Department, Salary ,
            RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RANKNO
     FROM @Employees
  ) AS T
WHERE RANKNO = 2
  • 值得參考的解法
SELECT EmployeeID,EmployeeName,Department,Salary
FROM @Employees e0
WHERE
      (
        SELECT Count(DISTINCT e1.Salary) 
        FROM @Employees e1 
        WHERE e1.Salary >= e0.Salary 
                AND e1.Department = e0.Department
      ) = 2
ORDER BY Department, EmployeeID
  • 結果
EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00
    參考資料: 

沒有留言:

張貼留言