ORDER BY 進階排序

鍾文彥 2020/07/23 10:19:08
71

以下介紹幾種根據運算式或函數,使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,因為字串是以「逐碼比對」的方式決定順序,所以就會踩到上例的地雷。

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

鍾文彥
Matt
2020/07/24 23:49:19

最後一個範例,可以使用 CAST 來處理。但是要留意轉換失敗的狀況。
如果是 MSSQL 2012 之後的版本,可以用 TRY_CAST 會很方便。

DECLARE @tbNum TABLE(Num VARCHAR(5));
INSERT INTO @tbNum(Num) VALUES('!');
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('**');

SELECT [Num], ISNULL(TRY_CAST([Num] AS int), -99AS a
FROM @tbNum
ORDER BY a DESC;