ORDER BY 進階排序

以下介紹幾種根據運算式或函數，使ORDER BY子句變化出一些自訂規則的進階版排序。

●優先區域排序

先劃分出優先的區域，先根據區域的優先性排序後，再針對區域內的值再排序。

範例：24小時營業的商店以每2小時為一班劃分為12組班表，依早到晚來排序，但是每天的1000~2000為來客量尖峰期，需要將這些時段擺在最上方。

DECLARE @tbSchedule TABLE(Interval VARCHAR(4));
INSERT INTO @tbSchedule(Interval) VALUES('0000');
INSERT INTO @tbSchedule(Interval) VALUES('0200');
INSERT INTO @tbSchedule(Interval) VALUES('0400');
INSERT INTO @tbSchedule(Interval) VALUES('0600');
INSERT INTO @tbSchedule(Interval) VALUES('0800');
INSERT INTO @tbSchedule(Interval) VALUES('1000');
INSERT INTO @tbSchedule(Interval) VALUES('1200');
INSERT INTO @tbSchedule(Interval) VALUES('1400');
INSERT INTO @tbSchedule(Interval) VALUES('1600');
INSERT INTO @tbSchedule(Interval) VALUES('1800');
INSERT INTO @tbSchedule(Interval) VALUES('2000');
INSERT INTO @tbSchedule(Interval) VALUES('2200');

SELECT Interval,
	ROW_NUMBER() OVER(ORDER BY Interval) OriSeq --原序號
FROM @tbSchedule
ORDER BY
	CASE WHEN Interval BETWEEN '1000' AND '2000' THEN 0 --1000~2000設定為0(置前)
		ELSE 1 --其他時段設定為1(置後)
	END,
	Interval; --區域內依時段早到晚排序

執行結果：

OriSeq為原本的序號當作比對用。這裡可以看到以時段早到晚排序的前提之下，1000~2000尖峰時段整個往前提了。

●資料值分段排序

是指以特定值做分段，每一段的升降冪排序可以彼此不同，此種排序方式實務面應用較少。

範例：數字1到6，3以下的升冪排序，超過3的降冪。

DECLARE @tbNum TABLE(Num INT);
INSERT INTO @tbNum(Num) VALUES(1);
INSERT INTO @tbNum(Num) VALUES(2);
INSERT INTO @tbNum(Num) VALUES(3);
INSERT INTO @tbNum(Num) VALUES(4);
INSERT INTO @tbNum(Num) VALUES(5);
INSERT INTO @tbNum(Num) VALUES(6);

SELECT *
FROM @tbNum
ORDER BY
	CASE WHEN Num<=3 THEN 0 ELSE 1 END, --將3以下的部分置前
	CASE WHEN Num>3 THEN Num*-1 ELSE Num END; --超過3的部分加負號，大的就會變小的，達成降冪的效果

執行結果：

另外，如果資料為文字型態，無法以負號達成排序的效果，就必須出動ROW_NUMBER()來協助處理。範例如下：

DECLARE @tbVal TABLE(Val VARCHAR(1));
INSERT INTO @tbVal(Val) VALUES('A');
INSERT INTO @tbVal(Val) VALUES('B');
INSERT INTO @tbVal(Val) VALUES('C');
INSERT INTO @tbVal(Val) VALUES('D');
INSERT INTO @tbVal(Val) VALUES('E');
INSERT INTO @tbVal(Val) VALUES('F');

SELECT *
FROM @tbVal
ORDER BY
	CASE WHEN Val<='C' THEN 0 ELSE 1 END, --C以下的部分置前
	CASE WHEN Val<='C' THEN ROW_NUMBER() OVER(ORDER BY Val) --C以下的升冪
		ELSE ROW_NUMBER() OVER(ORDER BY Val DESC) --D以上的降冪
	END;

執行結果：

●動態欄位排序

決定排序欄位時通常會明確指定某個欄位，例如ORDER BY Seq DESC。以下介紹如何根據條件動態決定要排序的欄位。

範例：查詢員工主檔，將已離職的員工依離職日排序，而在職員工則依員工編號排序。

DECLARE @tbEmployees TABLE(
	EmpNo VARCHAR(3),
	EmpName NVARCHAR(10),
	ResignDate DATETIME);
--尚在職的員工離職日預設為9999-12-31
INSERT INTO @tbEmployees(EmpNo,EmpName,ResignDate) VALUES('001','王一一','9999-12-31');
INSERT INTO @tbEmployees(EmpNo,EmpName,ResignDate) VALUES('002','林二二','2020-03-01');
INSERT INTO @tbEmployees(EmpNo,EmpName,ResignDate) VALUES('003','許三三','9999-12-31');
INSERT INTO @tbEmployees(EmpNo,EmpName,ResignDate) VALUES('004','陳四四','2020-01-02');
INSERT INTO @tbEmployees(EmpNo,EmpName,ResignDate) VALUES('005','李五五','2020-05-01');
INSERT INTO @tbEmployees(EmpNo,EmpName,ResignDate) VALUES('006','吳六六','9999-12-31');

SELECT *
FROM @tbEmployees
ORDER BY
	CASE WHEN ResignDate<'9999-12-31' THEN FORMAT(ResignDate,'yyyy-MM-dd') --已離職的依離職日排序
		ELSE EmpNo --在職的依員工編號排序
	END;

執行結果：

●自訂字串位置排序

此種排序方式可以給定一組字串，並依照這組字串的出現順序作為排序依據。

範例：方位給定「東南西北」的順序作排序。

先產生測試資料：

DECLARE @tbDirection TABLE(DirName NVARCHAR(3));
INSERT INTO @tbDirection(DirName) VALUES('東');
INSERT INTO @tbDirection(DirName) VALUES('西');
INSERT INTO @tbDirection(DirName) VALUES('南');
INSERT INTO @tbDirection(DirName) VALUES('北');

直接針對方位去作排序，測試結果如何：

SELECT DirName,
	ROW_NUMBER() OVER(ORDER BY DirName) Seq
FROM @tbDirection;

現在以「東南西北」來排序，程式碼改寫如下：

SELECT DirName,
	ROW_NUMBER() OVER(ORDER BY DirName) Seq
FROM @tbDirection
ORDER BY CHARINDEX(DirName,'東南西北');
--ORDER BY INSTR('東南西北',DirName); ← ORACLE寫法

執行結果：

Seq為原排序，可以看出方位的排序已經依照設定重新排過了。

另外取得字串位置的函數，MSSQL為CHARINDEX()，在ORACLE則使用INSTR()。

●彙總函數

非常常用的一種排序方式，尤其是報表類，將彙總加權影響力作降冪排序是很常見的需求。

範例：查詢訂單檔，依照客戶下訂量的多寡降冪排序。

DECLARE @tbOrder TABLE(
	CustName NVARCHAR(3),
	Qty INT);
INSERT INTO @tbOrder(CustName,Qty) VALUES('李一一',1);
INSERT INTO @tbOrder(CustName,Qty) VALUES('王二二',3);
INSERT INTO @tbOrder(CustName,Qty) VALUES('陳三三',3);
INSERT INTO @tbOrder(CustName,Qty) VALUES('李一一',4);
INSERT INTO @tbOrder(CustName,Qty) VALUES('王二二',1);
INSERT INTO @tbOrder(CustName,Qty) VALUES('張九九',8);

SELECT CustName,
	SUM(Qty) Qty
FROM @tbOrder
GROUP BY CustName
ORDER BY SUM(Qty) DESC;

執行結果：

以上介紹了數種ORDER BY的變化型，主要是用在一些自訂的需求。

但是對於排序的使用上，建議欄位盡量以它本身應當的資料型態儲存。過去偶爾會發生數值被當作字串儲存的例子，這在排序上會存在一些盲點。

例如以下的例子：

DECLARE @tbNum TABLE(Num VARCHAR(5));
INSERT INTO @tbNum(Num) VALUES('1');
INSERT INTO @tbNum(Num) VALUES('12');
INSERT INTO @tbNum(Num) VALUES('123');
INSERT INTO @tbNum(Num) VALUES('2');
INSERT INTO @tbNum(Num) VALUES('3');
INSERT INTO @tbNum(Num) VALUES('4');

SELECT Num
FROM @tbNum
ORDER BY Num DESC;

預期的排序應該是123、12、4、3、2、1，因為字串是以「逐碼比對」的方式決定順序，所以就會踩到上例的地雷。

所以使用上盡量就以資料本身的型態作儲存就可以了。