SQL 找出最高學歷問題

情境說明

在實際應用中，我們常面臨需要找出某個人的最高學歷、最後工作經驗等問題(如下圖)，這些涉及到一個人對應多筆有先後順序的明細資料，你可能會常看到先撈出所有員工資料，然後在程式端進行迴圈的篩選，但若資料量過大，這種方式可能面臨效能問題，因此更好的解決方法是在資料過篩選完必要的結果後，再將其拋給程式處理。

這篇文章我要記錄一下兩個常用的寫法，假設我們有三張資料表 #TempEmployee(員工資料)、#TempEduType(學位類型)、#TempEduSchool(員工學歷)，建立測試資料表可以參考本文最下方的語法。

1. 我們常見的子查詢的寫法，但缺點是一次只能顯示一個欄位，若要同時顯示最高學歷和其學校名稱，則必須寫兩筆子查詢。

   Select emp.*, 
   	(
   		Select Top 1 b.[Type] 
   		From #TempEduSchool a 
   		Inner Join #TempEduType b on b.[Code]=a.[Type] 
   		Where emp.EmpNo=a.EmpNo 
   		Order by b.[Level] Desc 
   	) as [MaxType], 
   	(
   		Select Top 1 a.[SchoolName] 
   		From #TempEduSchool a 
   		Inner Join #TempEduType b on b.[Code]=a.[Type] 
   		Where emp.EmpNo=a.EmpNo 
   		Order by b.[Level] Desc 
   	) as [SchoolName]
   From #TempEmployee emp

2. 使用 Outer Apply(效果類似 Left Join) or Cross Apply(效果類似 Inner Join) 的方式，詳細可參考 SQL SERVER 2005 中 Apply的用法。

   Select * 
   From #TempEmployee emp
   Outer Apply  
   (
   	Select Top 1 b.[Type] as [MaxType], a.[SchoolName]
   	From #TempEduSchool a 
   	Inner Join #TempEduType b on b.[Code]=a.[Type] 
   	Where emp.EmpNo=a.EmpNo 
   	Order by b.[Level] Desc 
   ) sub

建立本文範例資料表

相關語法如下參考，使用完畢記得刪除不要的測試資料表喔。

-- 建立測試用暫存資料表
Create Table #TempEmployee ([EmpNo] varchar(10),  [EmpName] nvarchar(20))
Create Table #TempEduType ([Code] varchar(10), [Level] smallint,  [Type] nvarchar(20))
Create Table #TempEduSchool([EmpNo] varchar(10), [Type] varchar(10),  [SchoolName] nvarchar(20))

-- 建立測試資料
Insert Into #TempEmployee values('001', N'Lawrence')
Insert Into #TempEmployee values('002', N'Olivier')
Insert Into #TempEmployee values('005', N'John')
Insert Into #TempEmployee values('006', N'Max')

Insert Into #TempEduType values('A', 10, N'高中')
Insert Into #TempEduType values('B', 20, N'大學')
Insert Into #TempEduType values('C', 30, N'碩士')
Insert Into #TempEduType values('D', 40, N'博士')

Insert Into #TempEduSchool values('001','B', N'空軍機校')
Insert Into #TempEduSchool values('001','C', N'成功大學')
Insert Into #TempEduSchool values('002','B', N'東吳大學')
Insert Into #TempEduSchool values('005','C', N'輔仁大學')

-- 刪除測試資料表 
Drop Table #TempEmployee
Drop Table #TempEduType
Drop Table #TempEduSchool