本文將介紹在ORDER BY語法中，應用函數或CASE運算式的運算及轉換將資料排序順序依訂定規則排列，為達成自訂規則排序的需求，概念是將重要資料集透過CASE運算式判斷轉換為優先順序，以0代表重要其他則為1建置虛擬資料欄位（未必會顯示）並據此為排序依據，語法概念如下：
ORDER BY
CASE WHEN <Condition> THEN 0 ELSE 1 END--重要資料以條件式轉換為0，其他為1
[,ColumnName]
|
1. CASE運算式
l
優先區域排序
優先區域排序是將使用者重要資訊優先顯示，其他資訊則按照原排序方式。首先，將依重要性劃分重要、次要區域並排序，再進行區域內排序，概念如下圖所示。
以數值1~5為例，將值3、4視為重點將置前處理，後續將以兩個範例進行說明。
|
1.移至前方
|
|||||||
|
6
6
|
|||||||
2.區域排序
|
||||||||
首先，以電視公司廣告精華時段為例，每天18:00後為一天的廣告精華時段，此為銷售重點並希望此時段置於廣告明細最上方。SQL語法及結果如下所示：
SELECT ProgID
, ProgTime
, ROW_NUMBER() OVER (ORDER BY ProgTime) Org_Seq –原排序(比對用)
FROM Program
ORDER BY
CASE WHEN SUBSTRING(ProgTime,1,2) >='18' THEN 0 --將1800後設為0 (置前)
ELSE 1 --其他為1 (置後)
END --重點(1). 將廣告精華時段優先顯示
, ProgTime --重點(2). 兩時段(精華/非精華)則仍依節目時間
|
|
|||||||||||||||||||||||||||||||||||||
說明：
l 25:00是電視公司收視率調查的特殊需求，因此採用字串型態儲存。
l SUBSTRING為MSSQL語法，ORACLE請改用SUBSTR即可。
|
NULL值排序
此外，對於資料值中含有NULL之排序，由於ANSI並未針對NULL排序優先順序進行定義，ORACLE預設為最大值，對MSSQL則視為最小值。兩種不同資料庫執行基本SQL排序指令結果如下。
SELECT *
FROM Employees
ORDER BY Email
|
MSSQL (視NULL為最小值)
|
ORACLE (視NULL為最大值)
|
|||||||||||||||||||||||||||||||||
|
|
ORACLE可於排序欄位中指定NULL值為最優先或最後之順序，使用方法整理如下：
順序
|
關鍵字
|
SQL範例
|
最優先
|
NULLS FIRST
|
SELECT *
FROM
MAIL
ORDER BY
EMAIL NULLS FIRST
|
最後
|
NULLS LAST
|
SELECT *
FROM MAIL
ORDER BY
EMAIL DESC NULLS
LAST
|
應用NULLS FIRST或NULLS LAST關鍵字時，會強制將NULL值置於結果集中最前或最後，其優先權於高於原欄位所指定的昇冪（ASC）或降冪（DESC）排序方向。
MSSQL無對應之NULL FIRST或NULLS LAST關鍵字，如前述MSSQL預設將NULL值置於最前，如欲置於最後方時，則需搭配CASE運算式判斷Email是否為NULL，可將NULL值視為優先區域而置於後方，即可達成ORACLE的NULLS LAST的功能。
SELECT Emp_Name, Email
FROM Employees
ORDER BY
CASE WHEN Email IS NULL THEN 1
ELSE 0
END -- 達成NULLS LAST功能
, EMAIL
|
l
資料值分段排序
資料值分段排序，是指在同一欄位中以特定值為分段，一段是以昇冪（ASC）排序、另一段則是以降冪（DESC）排序，概念如下所示，以1~5的數列為例，以1~3為昇冪（ASC）排序區域，而4、5則為降冪（DESC）。
|
6
|
||||||
5
|
實務應用範例較少，以下將分別針對數值欄位（N）及文字型態（Val）進行排序。
數值型態是以1~5數列進行排序，1~3是以昇冪，而4、5則是以降冪排序，SQL語法、原資料及排序後結果如下所示：
SQL
|
原資料
|
排序後
|
|||||||||||||||
SELECT N
--, CASE WHEN N>3 THEN 1 ELSE 0 END
--, CASE WHEN N>3 THEN 0-N ELSE N END
FROM #Test
ORDER BY
CASE WHEN N>3 THEN 1 ELSE 0 END --重點2. (1,2,3)(4,5)兩區域前後
, CASE WHEN N>3 THEN -N ELSE N END --重點1. (1,2,3)(5,4)區域內
|
|
|
以上在 (1, 2, 3) 區間是以昇冪排序，(4,
5) 區間則是以降冪排序，利用CASE 運算式將大於3以上的 (4, 5) 區間運用正負值轉換 (-1*N) 的作法，將資料變化成大小相反的結果 (-4, -5)，至於 (1, 2, 3) 區間則按照原昇冪排序，如SQL指令的重點2。
但前述轉換後兩區域 (1, 2, 3) 及
(-4, -5) 會造成兩區域對調的問題，因此，將運用先前『優先區域排序』所探討的作法，將轉換後負數資料區 (-4, -5) 置於後方，如重點1。
另外，文字型態可利用ROW_NUMBER函數產生序號的方式進行處理，ROW_NUMBER函數還可支援降冪排序，可將資料區分成 (A, B, C) 及
(D, E) 兩個區域，(D, E) 區域是以昇冪，(C, B, A) 區域則以降冪，再行組合即可。SQL語法、原資料及排序後結果如下所示：
SQL
|
原資料
|
排序後
|
|||||||||||||||
SELECT Val
FROM #Test
ORDER BY
CASE WHEN Val IN ('D','E') THEN 1
ELSE 0
END--重點2. (A, B, C)(D, E)兩區域前後
, CASE WHEN Val <'D' THEN
ROW_NUMBER() OVER (ORDER BY Val DESC)
ELSE ROW_NUMBER() OVER (ORDER BY Val ASC)
END--重點1. (C, B, A)(D, E)區域內
|
|
|
在此將利用ROW_NUMBER函數可以指定昇、降冪排序的方式，兩個區域各產生1組序號並據此排序。重點與數字排序類似，由於 (D, E) 區域的資料會依昇冪排序，可用一點小技巧將SQL指令中的重點2併入重點1，請自行嘗試完成。
l
動態選擇排序欄位
前述排序方法，需明確指定排序所依據欄位，以進行排序。
在此將介紹如何動態依條件動態決定排序欄位名稱或運算式名稱，以下應用CASE WHEN指令將以欄位特定值選擇排序所使用欄位，已離職的員工以部門（Dept_Id）進行排序，已離職員工則按離職日期排序。
判斷式（是否離職）
|
排序依據欄位
|
離職
|
離職日期
|
在職
|
部門代碼
|
SQL如下：
SELECT Emp_No, Emp_Name, Dept_Id, Date_Leave
FROM Employees
WHERE 1=1
ORDER BY CASE WHEN Date_Leave < '9999-12-31' THEN
FORMAT(Date_Leave, 'yyyy-MM-dd') --2.離職:按離職日
ELSE CAST(Dept_Id AS varchar(10)) --1.在職:按部門
END
, Emp_No
註: Date_Leave離職日預設9999-12-31。
|
請注意CASE WHEN中資料型別處理問題，需進行額外處理，另外可參考先前範例，採二組ROW_NUMBER()產生流水號做為排序依據。
2. 字串位置函數（INSTR、CHARINDEX）
以下將利用字串函數取得目標字串與特定字串中位置的作法進行排序。以下將以春、夏、秋、冬四季進行排序，以子查詢產生資料集，在此將應用UNION指令將資料聯集以產生測試資料集，結果如下表所示。UNION指令有排序的特性，結果與原本資料順序不同。在排序部分，則透過字串位置函數取出相對於『春天夏天秋天冬天』字串中的位置進行排序，MSSQL是採用CHARINDEX函數，ORACLE則應用INSTR函數，排序結果如下：
MSSQL
|
ORACLE
|
SELECT *
, CHARINDEX(Season, '春天夏天秋天冬天') Pos
, ROW_NUMBER() OVER(ORDER BY Season) "ex
ante"
FROM
(
SELECT '春天' Season
UNION
SELECT '夏天'
UNION
SELECT '秋天'
UNION
SELECT '冬天'
) A -- 子查詢，產生資料集
ORDER BY
CHARINDEX(Season, '春天夏天秋天冬天')
|
SELECT A.*
, INSTR('春天夏天秋天冬天', Season) Pos
, ROW_NUMBER() OVER(ORDER BY Season) "ex
ante"
FROM
(
SELECT '春天' Season
FROM DUAL
UNION
SELECT '夏天' FROM DUAL
UNION
SELECT '秋天' FROM DUAL
UNION
SELECT '冬天' FROM DUAL
) A-- 子查詢，產生資料集
ORDER BY
INSTR('春天夏天秋天冬天', Season)
|
前述SQL中，額外應用MSSQL2005、ORACLE8i起所提供的ROW_NUMBER函數，取得以Season所排序的結果（與子查詢順序相同），ROW_NUMBER的使用方法可參閱《次序函數》，排序結果如下：
資料集（子查詢）
|
查詢（排序）結果
|
|||||||||||||||||||||
|
|
|||||||||||||||||||||
註：按季節中文排序
|
註：按季節意義排序
|
3
COUNT函數（彙總函數）
對於一般的總合報表而言，將依彙總指標影響程度較高的進行排序，以下訂單表格將依客戶訂單多寡排序，將訂單數量數量較高的客戶置於表格上方。SQL語法如下所示：
SQL
|
結果
|
||||||||||||||||
SELECT Cust_Name, SUM(Qty) Qty, Count(*) Cnt
FROM Orders
GROUP BY
Cust_Name
ORDER BY
Count(*) DESC
|
|
利用CASE運算式進行資料轉換將重要的資料置於指定位置，此外，也可用於動態決定排序所使用的欄位，或應用彙總運算結果做為排序依據，群組運算結果可直接應用於排序依據。
沒有留言:
張貼留言