集合（SET）運算中使用排序注意事項

集合(SET)指令的目的是將兩個(含以上)SQL指令所產生的結果合併起來，由於是將兩個(含以上)的資料集進行合併，因此操作上些許限制，如在資料欄位數量上必需相等，型態上則需必需相容，在排序上也與單一查詢資料集有些許不同。

一般查詢情況下，排序在除可依據資料表之欄位名稱外，尚有SELECT清單位置（Postition）、運算式（expression : expr）及欄位/運算式別名（Alias）等方法，ORDER BY所參照之欄位並非限定存在於SELECT 清單中，但SELECT DISTINCT則除外。而集合運算(SET)如需使用運算式（expression: expr）進行排序時，則此運算式必須存在於SELECT清單中，後續將進行數項測試以說明此限制條件。

以下針對單一及集合運算進行測試，單一查詢可順利執行，如預期A30排於第1位，而集合運算MSSQL與ORACLE兩者均發生錯誤，SQL及執行結果如下所示:

#	SQL/結果	執行結果
一般SQL (可執行)	SELECT VAL FROM Test WHERE 1=1       AND VAL LIKE 'A%'      ORDER BY CASE WHEN VAL ='A30' THEN '000' ELSE VAL END --ORDER BY VAL　-- Column Name / Alias --ORDER BY 1　  -- Column Position
集合運算 （錯誤）	SELECT VAL   --, CASE WHEN VAL ='A38' THEN '000' ELSE VAL END FROM Test WHERE 1=1       AND VAL LIKE 'A%' UNION ALL SELECT VAL   --, CASE WHEN VAL ='A38' THEN '000' ELSE VAL END FROM Test WHERE 1=1       AND VAL LIKE 'B%' ORDER BY CASE WHEN VAL ='A30' THEN '000' ELSE VAL END	ü  MSSQL Msg 104, Level 16, State 1, Line 12 ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator. ü  ORACLE ORA-01785: ORDER BY item must be the number of a SELECT-list expression

MSSQL

訊息104，層級16，狀態1，行14

如果陳述式中包含UNION、INTERSECT 或EXCEPT 運算子，則選取清單中必須有ORDER BY 項目。

ORACLE

ORA-01785: ORDER BY 項目必須是 SELECT-list 表示式中的數字

由MSSQL錯誤訊息，可清楚得知在集合運算中採用排序指令ORDER BY 時，其中必須存在（出現）於SELECT清單中，但由ORACLE的錯誤訊息則較以理解其原由。

以下進行3種測試，分析說明兩種資料庫之差異:

#	SQL/結果	執行結果
1	運算式（expression: expr） SELECT VAL   , CASE WHEN VAL ='A38' THEN '000' ELSE VAL END FROM Test WHERE 1=1       AND VAL LIKE 'A%' UNION ALL SELECT VAL   , CASE WHEN VAL ='A38' THEN '000' ELSE VAL END FROM Test WHERE 1=1       AND VAL LIKE 'B%' ORDER BY CASE WHEN VAL ='A38' THEN '000' ELSE VAL END --測試1	ü  MSSQL（可執行） ü  ORACLE（錯誤） ORA-01785: ORDER BY item must be the number of a SELECT-list
2	運算/欄位別名（Alias） SELECT VAL        , CASE WHEN VAL ='A38' THEN '000' ELSE VAL END Col FROM Test WHERE 1=1       AND VAL LIKE 'A%' UNION ALL SELECT VAL      , CASE WHEN VAL ='A38' THEN '000' ELSE VAL END FROM Test WHERE 1=1       AND VAL LIKE 'B%' ORDER BY Col　--測試2	MSSQL/ORACLE（可執行）
3	SELECT清單位置（Position） SELECT VAL --位置1      , CASE WHEN VAL ='A38' THEN '000' ELSE VAL END Col --位置2 FROM Test WHERE 1=1       AND VAL LIKE 'A%' UNION ALL SELECT VAL        , CASE WHEN VAL ='A38' THEN '000' ELSE VAL END FROM Test WHERE 1=1       AND VAL LIKE 'B%' ORDER BY 2　--測試3	MSSQL/ORACLE（可執行）

由測試2、3可得知，集合運算(SET)中排序以運算/欄位別名（Alias）或SELECT清單位置（Position）兩種資料庫均可順利執行；但對於由測試1中的排序直接使用運算式（expression: expr）則兩者不同，MSSQL仍支援，但ORACLE發生如同先前之『ORA-01785: ORDER BY item must be the number of a SELECT-list expression』錯誤。

集合運算(SET)如需使用運算式（expression: expr）排序時，則此運算式必須存在於SELECT清單中，MSSQL可如同一般查詢之排序，可於ORDER BY子句中使用運算式、運算欄位別名及運算欄位位置等3種方法，而ORACLE只可使用運算欄位別名及運算欄位位置等2種方法。

資料產生(以ORALCE為例，MSSQL改以INTO Test)

CREATE TABLE Test

AS

SELECT 'A42' VAL

FROM DUAL

UNION ALL

SELECT 'A30'

FROM DUAL

UNION ALL

SELECT 'A28'

FROM DUAL

UNION ALL

SELECT 'B26'

FROM DUAL

UNION ALL

SELECT 'B90'

FROM DUAL