iT邦幫忙

DAY 17
14

T-SQL 應用之美系列 第 17

如何使用次序函數於查詢資料時,針對某個欄位進行排名(下)

上次分享了使用 Microsoft SQL Server 2005 才開始提供的**「次序函數」**:ROW_NUMBER()、RANK()、DENSE_RANK(),於查詢資料的同時,針對某個欄位進行排名,這次要分享最後一個函數:NTILE()。

附註:Microsoft SQL Server 2008 已經把**「次序函數」改名成「排名函數」**。
如果沒有看過上一篇:如何使用次序函數於查詢資料時,針對某個欄位進行排名(下)的邦友,記得要去看,免得在執行這次文章內的程式碼時,發生錯誤,同時也可瞭解原來 SQL Server 居然有這麼好用的函數。

言歸正傳,如果要把結果分成特定數目的組,可以使用 NTILE() 函數,例如下面的程式碼會依照年齡來排名,將資料分成 58 組:

SELECT [依照年齡排名分成 58 組] = NTILE(58) OVER (ORDER BY dbo.fn_GetAge(BirthDate)),
	年齡 = dbo.fn_GetAge(BirthDate),
	員工編號 = EmployeeID,
	性別 = CASE Gender
			WHEN 'M' THEN N'男'
			WHEN 'F' THEN N'女'
		END,
	婚姻 = CASE MaritalStatus
			WHEN 'S' THEN N'單身'
			WHEN 'M' THEN N'已婚'
		END
FROM HumanResources.Employee

執行結果的部分畫面:

從結果可以看出來,一共有 290 筆資料,要分成 58 組,所以每組會有 5 筆資料。

其實,NTILE() 函數最常用於找出某一組中的資料,例如依照年齡排名,把資料分成 58 組,然後要找出第 2 組中的資料:

SELECT 年齡 = dbo.fn_GetAge(BirthDate),
	員工編號 = EmployeeID,
	性別 = CASE Gender
			WHEN 'M' THEN N'男'
			WHEN 'F' THEN N'女'
		END,
	婚姻 = CASE MaritalStatus
			WHEN 'S' THEN N'單身'
			WHEN 'M' THEN N'已婚'
		END
FROM (SELECT 分組 = NTILE(58) OVER (ORDER BY dbo.fn_GetAge(BirthDate)),
	BirthDate, EmployeeID, Gender, MaritalStatus
	FROM HumanResources.Employee) temp
WHERE 分組 = 2

執行結果的畫面:

附註:

  1. 本篇與上篇所提到的 4 個**「次序函數」**都可以在 ORDER BY 的前面,使用 PARTITION BY 對資料進行分組。
  2. 在找資料時,發現有篇 Blog 文章「SQL Server 2005 - 如何讓 SELECT 查詢結果額外增加自動遞增序號」,針對資料表本身沒有自動編號欄位的時候,應該要怎麼作,才能夠讓 SELECT 查詢的結果可以額外增加自動遞增序號,有詳細的說明,連使用 Microsoft SQL Server 2000 的方法都有說明。

上一篇
如何使用次序函數於查詢資料時,針對某個欄位進行排名(上)
下一篇
如何使用次序函數刪除重複的記錄
系列文
T-SQL 應用之美30

尚未有邦友留言

立即登入留言