不是每個資料表都會有編號的欄位,這次要分享如何使用 Microsoft SQL Server 2005 新增的**「次序函數」**,在查詢資料的同時,針對某個欄位進行排名。
附註:
這 4 種次序函數的定義,在 Microsoft SQL Server 2005 線上叢書已經有相關的範例與定義說明,所以想要瞭解的邦友,請自行閱讀相關的超連結,畢竟這次的主題是「如何使用次序函數於查詢資料時,針對某個欄位進行排名」。
為了方便解說,先在 AdventureWorks 資料庫中,建立在「使用流程控制:BEGIN...END 與 RETURN」討論過的自訂計算年齡函數:
USE AdventureWorks
GO
-- 建立自訂函數
CREATE FUNCTION dbo.fn_GetAge(
@myDate datetime)
RETURNS int
AS
BEGIN
-- 宣告變數
DECLARE @age int, @day datetime
-- 以「年」為單位計算出年齡
SET @age = DATEDIFF(yy, @myDate, getdate()) -
CASE WHEN @day < DATEADD(yy, DATEDIFF(yy, @myDate, @day), @myDate)
THEN 1
ELSE 0
END
RETURN @age
END
GO
接著要從 HumanResources.Employee 資料表中,透過上面建立的 fn_GetAge 函數,依照年齡由小至大,產生排名的欄位:
SELECT 依照年齡排名 = ROW_NUMBER() 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
執行結果的部分畫面:
從結果中,可以看到排名是依照年齡來排的,會從 1 開始排起,如果年齡一樣時,排名並不會一樣。
如果不想依照年齡來排,只是想要有一個編號的欄位,就可以把 ORDER BY 裡面的 dbo.fn_GetAge(BirthDate) 改成一個永遠會成立且只會傳回 1 個結果的查詢,例如改成 SELECT 0:
SELECT 編號 = ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
年齡 = 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
執行結果的部分畫面:
大家有沒有發現到結果的第 1 個欄位「編號」會跟第 3 個「員工編號」欄位的值一樣?所以說,如果資料表沒有所謂的自動編號欄位時,就可以用這個方法來產生一個自動編號欄位。
如果我們希望先依照性別進行男與女的資料分組,然後再依照年齡來排名,只要在 ORDER BY 的前面,多加入 PARTITION BY Gender:
SELECT 依照性別分組後的年齡排名 = ROW_NUMBER() OVER (PARTITION BY Gender 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
執行結果的部分畫面:
PARTITION BY 的欄位可以有多個,例如先依照性別分組,再依照婚姻來分組:
SELECT 依序以性別與婚姻分組的年齡排名 = ROW_NUMBER() OVER (PARTITION BY Gender, MaritalStatus 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
執行結果的部分畫面:
如果當年齡一樣時,排名也要一樣的話,要把第一個程式碼中,原本的 ROW_NUMBER() 換成 RANK() 就可以了:
SELECT [依照年齡排名(年齡一樣排名也會一樣)] = RANK() 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
執行結果的部分畫面:
從執行結果,可以看出當排名一樣時,中間會遺漏幾個排名,這個常見於學校的考試排名。但是,如果不希望遺漏部分的排名,要把 RANK() 換成 DENSE_RANK():
SELECT [依照年齡排名(年齡一樣排名也會一樣)] = DENSE_RANK() 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
執行結果的部分畫面:
另外的內容,請看下篇。