第 10 章 更多的查詢技巧

本章提要 10-1 用 UNION 合併多個查詢結果 10-2 子查詢 Subquery 10-3 使用 SQL Server Management Studio 管理工具設計 SQL 查詢 10-4 T-SQL 的常數 10-5 隱含式型別轉換 10-6 T-SQL 的運算子

本章提要 10-7 運算子的優先順序 10-8 處理欄位中的 NULL 值 10-9 使用 APPLY 運算子在查詢時同時呼叫函數進行運算 10-10 排序函數：ROW_NUMBER( )、RANK() 與 DENSE_RANK() 10-11 使用 PIVOT 與 UNPIVOT 將直式資料表轉為橫式

10-1 用 UNION 合併多個查詢結果 UNION 可將多個 SELECT 敘述的查詢結果合併成一組。什麼意思呢？看下圖您就明瞭了：

10-1 用 UNION 合併多個查詢結果 看出來了嗎？UNION 是將多個查詢結果做 “上下垂直” 合併, 所以欄位數不會增加。 您可能會聯想到上一章介紹的 JOIN, 與 UNION 相較, JOIN 是將資料表的欄位做左右水平合併, 所以通常欄位數會增多。 總而言之, UNION 和 JOIN 並不相同, 兩者不要弄混了！

UNION 的條件與結果 UNION 的用意其實相當簡單, 但是要讓多個查詢結果能夠相安無事地合併起來, 則必須符合下列的條件： 欲合併的查詢結果, 其欄位數必須相同。 欲合併的查詢結果, 其對應的欄位 (如上圖的甲欄和丙欄、乙欄和丁欄) 一定要具備 "相容" 的資料型別, 即資料型別可以不同, 但兩者必須能夠互相轉換。

UNION 的條件與結果 至於合併後的結果會有什麼變化呢？在此我們也先做個說明, 讓各位有個心理準備： 合併結果的欄位名稱會以第一個查詢結果的欄位名稱為名, 其他查詢結果的欄位名稱則會被忽略掉。 合併時, 若對應的欄位具備不同的資料型別, 則 SQL Server 會進行相容性的型別轉換, 轉換的原則是以 "可容納較多資料的型別為主"。就拿上圖來說, 假設乙欄是 CHAR(10) 型別, 丁欄是 CHAR(20) 型別, 則合併後的乙欄便會是 CHAR (20) 型別。 在此處必須再次提醒您, 並不是每種資料型別都可以互相轉換。如果無法自動轉換, 除非我們介入強制轉換, 否則便會顯示錯誤訊息, 無法完成合併。

UNION 的語法 UNION 的語法如下：

UNION 的語法 select_statement：就是以 SELECT 開頭的查詢敘述, 語法和第 9 章介紹的一樣, 但用法有一些差異： GROUP BY 和 HAVING 子句只能用在個別的 select_statement 中, 不可用於整個 UNION 敘述的最後。 ORDER BY 及 COMPUTE 子句則只能用在整個敘述的最後, 針對最後的合併結果做排序或計算, 不能用在個別的 select_statement 中。 只有第一個 select_statement 可以設定 INTO 子句。

UNION 的語法 ALL：如果設定 ALL 參數, 最後的合併結果會將重複的記錄都顯示出來。如果不設定 ALL 參數, 則在合併結果中, 重複的記錄將只會顯示一筆。 小括弧 ( )：合併的順序原則上是 "由左至右", 但可利用小括弧 ( )來改變合併的優先順序。例如：select1 UNION (select2 UNION select3), 則會先合併 select2、select3, 得到的結果再和 select1 合併。

應用範例 現在我們就利用練習 01 資料庫中的合作廠商和客戶資料表, 來示範 UNION 的用法。右邊是合作廠商和客戶資料表現有的資料內容：

應用範例 假設我們想邀請合作廠商及客戶的聯絡人來參加公司尾牙, 則可以分別從合作廠商及客戶這兩個資料表查詢聯絡人的姓名、地址, 然後將這兩個查詢結果合併成一份邀請名單：

應用範例

應用範例 不刪除資料表合併後重複的資料 上例單純使用 UNION的合併結果會將重複的資料過濾掉, 只顯示一筆。 若要顯示全部的資料, 不論資料是否重複, 則可改用 UNION ALL 來合併：

應用範例

應用範例 加入臨時的資料 我們還可以利用 UNION, 在查詢結果上加入一些臨時資料 (這些臨時資料並不存在資料表中)。 例如我們要在邀請名單中再加入一位臨時客人 "王大砲"：

10-2 子查詢 Subquery 所謂子查詢 (Subquery), 是指包含在主要查詢中的另一個 SELECT 查詢。通常我們會利用子查詢先挑選出部份資料, 以做為主要查詢的資料來源或選取條件。 以下就來介紹子查詢的用法。 子查詢的語法與範例 子查詢的類型與處理方式 獨立子查詢與關聯子查詢

子查詢的語法與範例 子查詢的語法和 SELECT 敘述一樣, 但有下列的限制： 整個子查詢敘述需用小括弧 ( ) 括住。 子查詢中不能使用 COMPUTE、INTO 子句。 若子查詢中有用到 "SELECT TOP n...", 才可設定 ORDER BY 子句來排序。

子查詢的語法與範例 底下我們來看個子查詢的應用範例： 上例中, 我們的目的是要從訂單資料表查出每筆訂單訂購的產品總數, 可是每筆訂單的訂購產品及數量是儲存在訂購項目資料表中, 所以就利用子查詢先到訂購項目資料表中算出每筆訂單的訂購總數量。

子查詢的類型與處理方式 子查詢的傳回結果 (即查詢結果) 可分成 3 種類型：單一值、單欄的多筆資料、不限定欄數的多筆資料；而用來處理子查詢傳回結果的方法也分為 3 種： 方法 1：直接取值－直接使用子查詢的傳回值, 例如用 =、>、< 做比較, 或進行加減乘除等運算。 方法 2： 比對清單－使用 IN 、ALL、或 ANY (SOME) 運算子判斷某個值是否存在於傳回清單中, 其比對結果為 True 或 False。

子查詢的類型與處理方式 以上 3 種處理方法的適用時機如下表所示： 方法 3：測試存在－使用 EXISTS 運算子判斷是否有傳回資料, 其測試結果亦為 True 或 False。 以上 3 種處理方法的適用時機如下表所示：

子查詢的類型與處理方式 為了便於說明, 底下我們利用旗旗公司與標標公司資料表, 來示範上述 3 種處理方法。 旗旗公司與標標公司資料表目前的內容如下所示：

子查詢的類型與處理方式 直接取值的子查詢 如果子查詢會傳回單一值, 那麼該子查詢便可使用於任何允許運算式出現的地方, 而且不限 SELECT 敘述, 連 INSERT、UPDATE、DELETE 等敘述中都可使用這種子查詢。 我們來看幾個範例。

子查詢的類型與處理方式 第一個範例是計算標標公司每項產品價格所佔的百分比, 子查詢位於 SELECT 子句中, 用來算出所有產品的總和。

子查詢的類型與處理方式 第二個範例要找出旗旗公司的產品中, 價格比標標公司任何產品都貴的項目, 子查詢用於 WHERE 子句, 用來找出標標公司最貴的產品價格：

子查詢的類型與處理方式 比對清單的子查詢 如果子查詢會傳回單欄的一或多筆記錄 (型式像一份單欄的表格), 我們就可以用 IN、ALL、或 ANY(SOME) 運算子來與清單中的值做比對, 其中 ALL 和 ANY(SOME) 必須與比較運算子 (如 >、<=、= ) 一起使用。 此類子查詢可用於邏輯運算式中, 包括 SELECT、INSERT、UPDATE、DELETE 等敘述中的 WHERE 或 HAVING 子句。

子查詢的類型與處理方式 IN：IN 運算子可用來判斷給定的值是否在指定的子查詢中。 下面範例要查詢標標公司產品中, 旗旗公司也有的產品：

子查詢的類型與處理方式 ALL：ALL 運算子表示在查詢中的結果必須滿足子查詢中的所有結果。 例如下面範例先用子查詢找出旗旗公司所有在 410 元以上的產品價格, 然後再列出標標公司的產品中比這些價格都要便宜或相同的產品價格：

子查詢的類型與處理方式 ANY、SOME：ANY 運算子表示查詢結果只要滿足子查詢中任一個值即可；SOME 是 SQL-92 標準的用法, 意思與 ANY 相同。我們將上面範例的 ALL 改為 ANY, 看看結果有何變化：

子查詢的類型與處理方式 測試存在的子查詢 我們可以使用 EXISTS 來測試子查詢是否有傳回任何結果, 如果有結果就會傳回 TRUE, 沒有結果則傳回 FALSE。 這類的子查詢就不限定傳回值是單一值、單欄或多欄了, 只要有任何結果傳回即為 TRUE (即使傳回 NULL 值也算), 否則為 FALSE 。

子查詢的類型與處理方式 EXISTS 子查詢也是使用在邏輯運算式中, 下面範例可找出標標公司的產品中, 其價格在旗旗公司中超過 495 元的產品：

相同功能的不同查詢方式 其實同一個查詢結果可以用多種查詢方式來達成, 例如上一個範例也可改用下面兩種方法查詢： 或是：

獨立子查詢與關聯子查詢 從子查詢中是否使用到主查詢的資料, 可將子查詢分為獨立 (Independent) 子查詢和關聯 (Corelated) 子查詢兩種。

獨立子查詢與關聯子查詢 獨立子查詢 獨立子查詢是指可以脫離主查詢, 單獨執行的子查詢。 例如我們之前查詢標標公司的產品中, 旗旗公司也有生產的產品, 其敘述中的子查詢便是獨立子查詢：

獨立子查詢與關聯子查詢 關聯子查詢 關聯子查詢是指無法單獨存在的子查詢。舉例來說, 下面範例中的子查詢就無法單獨存在, 原因是子查詢使用了旗旗公司資料表的欄位, 而子查詢的 FROM 子句中並沒有該資料表, 所以如果單獨執行會產生錯誤：

獨立子查詢與關聯子查詢 關聯子查詢 關聯子查詢是指無法單獨存在的子查詢。舉例來說, 下面範例中的子查詢就無法單獨存在, 原因是子查詢使用了旗旗公司資料表的欄位, 而子查詢的 FROM 子句中並沒有該資料表, 所以如果單獨執行會產生錯誤：

獨立子查詢與關聯子查詢 請注意, 雖然內層查詢可以參考到外層查詢的資料表, 但反之卻不行, 也就是外層查詢不可以使用內層查詢的資料表。

10-3 使用 SQL Server Management Studio 管理工具設計 SQL 查詢 在 SQL Server Management Studio 中提供了相當好用的 "視覺化" SQL 設計工具, 稱為 "查詢設計工具", 這一節將帶您來看看如何使用查詢設計工具設計 SQL 查詢。 查詢設計工具 設計查詢的技巧 ─ 各窗格的操作 設定群組欄位 整個查詢敘述的屬性設定

查詢設計工具 首先請開啟查詢設計工具, 操作步驟如下：

查詢設計工具

查詢設計工具 查詢設計工具中畫分為 4 個窗格, 我們可利用上圖步驟 3 工具列上的 4 個按鈕來切換各窗格的顯示與隱藏：

查詢設計工具 請注意, 不管您如何切換, 查詢設計工具一定會保留一個窗格, 亦即我們無法同時將 4 個窗格都隱藏起來。 在查詢設計工具的 4 個窗格中, 圖表窗格、準則窗格和 SQL 窗格皆是設計查詢敘述的場所, 這 3 個窗格具有 “同步” 的作用 ─ 當您更動其中任一個窗格的內容後, 其他兩個窗格也會同步更新, 以達到一致的結果。 設計好查詢敘述後, 按下執行 SQL 鈕 執行, 則查詢結果便會顯示在結果窗格中。

設計查詢的技巧 ─ 各窗格的操作 接著我們分別來看各窗格的操作, 同時學習在各窗格中設計查詢的技巧。 圖表窗格 設計查詢的技巧 ─ 各窗格的操作 接著我們分別來看各窗格的操作, 同時學習在各窗格中設計查詢的技巧。 圖表窗格 在圖表窗格可選擇查詢的資料來源, 如資料表、檢視表 (請參閱第 11 章)、勾選要顯示的欄位、還可以建立資料表間的 JOIN 型態。 載入 / 移除資料來源 開啟查詢設計工具時, 我們當時所選擇的資料表即會自動載入結果窗格中。假如您還需要加入其它的資料來源, 請如下操作：

設計查詢的技巧 ─ 各窗格的操作 1). 按下加入資料表鈕 ：

設計查詢的技巧 ─ 各窗格的操作 2). 在列示窗中雙按您要加入的資料來源, 或選取資料來源再按加入鈕, 即可將資料來源載入圖表窗格中。 設計查詢的技巧 ─ 各窗格的操作 2). 在列示窗中雙按您要加入的資料來源, 或選取資料來源再按加入鈕, 即可將資料來源載入圖表窗格中。 3). 將需要的資料來源都載入後, 請按關閉鈕, 接著便可在圖表窗格中看到所選取的所有資料表：

設計查詢的技巧 ─ 各窗格的操作 在圖表窗格中的資料表有兩種顯示型態：一種是同時顯示資料表名稱及欄位名稱, 一種是僅顯示資料表名稱。您可用資料表標題列右側的 鈕 (或 鈕, 僅顯示資料表名稱時) 來切換；或者在標題列上按右鈕, 利用『資料行名稱』和『僅顯示名稱』這兩個命令來切換。 如果載入的資料來源用不到, 要將它移除時, 請在資料表標題列上按右鈕執行『移除』命令, 即可將該資料表移出圖表窗格。

設定資料表別名 若要為圖表窗格中的資料表設定資料表別名, 請先按一下資料表的標題列, 選取資料表, 然後於右邊屬性窗格 (或按屬性視窗鈕 )設定：

設計查詢的技巧 ─ 各窗格的操作 JOIN 資料表 設計查詢的技巧 ─ 各窗格的操作 JOIN 資料表 當圖表窗格中載入多個資料來源, SQL 窗格會自動為它們建立 CROSS JOIN。如果想要自己來 JOIN 資料表, 請如下操作：

設計查詢的技巧 ─ 各窗格的操作

設計查詢的技巧 ─ 各窗格的操作 假如要變更 JOIN 類型或修改 JOIN 的條件, 請在 JOIN 聯結線上按右鈕：

設計查詢的技巧 ─ 各窗格的操作 若上述兩個命令還無法滿足您設定 JOIN 的要求, 請如下操作：

設計查詢的技巧 ─ 各窗格的操作

設計查詢的技巧 ─ 各窗格的操作 若要移除 JOIN 聯結線, 請在聯結線上按右鈕執行『移除』命令, 則資料表間便會恢復成 CROSS JOIN 類型。

設計查詢的技巧 ─ 各窗格的操作 設定顯示欄位及排序欄位 設計查詢的技巧 ─ 各窗格的操作 設定顯示欄位及排序欄位 在圖表窗格中還可以設定查詢結果要顯示的欄位。假設要顯示員工資料表的姓名欄位和書籍資料表的書籍編號、書籍名稱欄位, 就在這 3 個欄位前的方框內打勾；若不顯示某欄位, 則在欄位前的方框取消勾選即可。 假如要在圖表窗格中設定排序欄位, 請先在資料表中要排序的欄位上按右鈕 (此處以書籍資料表的書籍編號欄位為例), 若要遞增排序, 就接著執行遞增排序命令；要遞減排序, 則執行遞減排序命令：

設計查詢的技巧 ─ 各窗格的操作

設計查詢的技巧 ─ 各窗格的操作 準則窗格 在準則窗格中主要是進行欄位方面的相關設定, 例如在此處也可以設定查詢結果要顯示的欄位、還可設定欄位別名、排序欄位、最重要的是可以設定查詢條件：

設計查詢的技巧 ─ 各窗格的操作

設計查詢的技巧 ─ 各窗格的操作 假設我們要查詢 "由女性員工負責, 而且價格 > 350 的書籍", 可如下設定：

設計查詢的技巧 ─ 各窗格的操作 如果要將某欄位整列移除, 可如下操作：

設計查詢的技巧 ─ 各窗格的操作 SQL 窗格 SQL 窗格可讓我們直接在此撰寫 SQL 語法。其實前兩個窗格只能算是設計 SQL 敘述的圖形化輔助工具, 您在前兩個窗格中所做的設定, 都會在 SQL 窗格中同步產生對應的 SQL 語法。 當按下工具列的執行鈕 執行查詢時, 便是執行此窗格中的 SQL 敘述。

設計查詢的技巧 ─ 各窗格的操作 我們除了可在 SQL 窗格中修改現成的語法內容, 其最大的用處是：圖表窗格和準則窗格無法支援的語法及敘述, 例如 UNION、CREATE DATABASE、ALTER TABLE ... , 都可以在此窗格中直接輸入。 但在 SQL 窗格中輸入圖表窗格和準則窗格無法支援的語法及敘述時, 會破壞 SQL 窗格與前兩個窗格的同步機制。 例如我們在 SQL 窗格中自行加入 UNION 的用法, 則執行時便會顯示如下的狀況：

設計查詢的技巧 ─ 各窗格的操作

設計查詢的技巧 ─ 各窗格的操作 在 SQL 窗格中, 您還可以按下驗證 SQL 語法鈕 , 來檢查 SQL 語法是否正確, 而不執行查詢。

設計查詢的技巧 ─ 各窗格的操作 結果窗格 結果窗格顧名思義就是顯示查詢結果的地方。在設計好查詢敘述後, 按下執行鈕 , 查詢結果就會出現在此窗格中。 在結果窗格中我們還可以新增、修改、刪除記錄的內容。

設定群組欄位 在查詢設計視窗中怎麼設定群組欄位 (GROUP BY 子句) 呢？ 假設我們想在員工及書籍資料表中查詢每個人完成了幾本書, 並將書籍的價格加總：

設定群組欄位

設定群組欄位

設定群組欄位

整個查詢敘述的屬性設定 另外還有一些查詢參數必須到整個查詢的屬性交談窗中設定, 例如 TOP、DISTINCT ... 等。 若要設定此類參數請不要選取查詢設計視窗中的任何物件, 然後按一下工具列的屬性視窗鈕 設定如下：

整個查詢敘述的屬性設定

10-4 T-SQL 的常數 有關查詢的型態、語法、和技巧, 前面已經介紹得差不多了, 接下來的內容則是要加強讀者對於運算式的設計技巧, 首先介紹常數的表達。 常數 (Constants) 就是以文、數字表達出來的字串、數值、日期等資料。依資料型別的不同, 常數也會有不同的表達方式, 底下將依序為您介紹。

T-SQL 的常數 字串常數 (Character string constants)：字串常數必須以單引號括起來, 若字串內容本身即含有單引號時, 可用連續兩個單引號來表示, 例如：

T-SQL 的常數 Unicode 字串常數 (Unicode string)：和字串常數表示法相同, 但必須在字串最前面加上一個大寫的 N：

T-SQL 的常數 二元碼常數 (Binary constants)：必須以 0x 開頭的 16 進位數值來表示, 前後不必加引號。例如： 位元常數 (bit constants)：只有 0 與 1 兩種值。

T-SQL 的常數 日期時間常數 (datetime constants)：必須用單引號括起來的日期或時間字串。日期的表示法主要有 3 種 (年份可任意使用 4 位數或 2 位數)：

T-SQL 的常數 其實 SQL Server 是很聰明的, 只要您輸入的日期能明顯分辨出年月日, 則不論是用哪種年月日順序都可正確輸入；只有當無法明確辨認時 (例如 ‘1/2/3’), 才會依預設的順序來解譯。 底下我們再列出一些關於日期、時間、及 "日期 + 時間" 的範例：

T-SQL 的常數 若只有日期沒有時間, 則會填入預設時間：‘12:00:00am’ (午夜)；若只有時間, 則預設日期為 ‘1900-1-1’。 另外, 當您執行查詢時, 所傳回的日期時間格式為 'yyyy-mm-dd hh:mi:ss' 或 'yyyy-mm-dd hh:mi:ss:mmm' (均為 24 小時制)。

T-SQL 的常數 整數常數 (Integer constants)：就是沒有小數的數值, 例如 1、3412。 精確位數常數 (Decimal constants)：是指 numeric 或 decimal 型別的資料, 以含有小數點的數值表示, 例如：12.3、67432.2345。 浮點常數 (Float and Real constants)：是指 float 或 real 型別的資料, 以科學記號表示, 例如：101.5E5、-0.5E-2 。

T-SQL 的常數 貨幣常數 (Money constants)：是指 money 或 smallmoney 型別的資料, 以 $ 開頭的數值表示, 例如：$12、$542023.14。 標記常數 (Uniqueidentifier constants)：就是 uniqueidentifier 型別的資料, 可以用字串或二元碼常數表示, 例如：' 6 F 9 6 1 9 F F-8 B 8 6-D 0 1 1-B4 2 D -00C04FC964FF' 或 0xff19966f868b11d0b42d00c04fc964ff。

更改年月日的順序 剛才提到的 3 種日期表示法, 除了第 3 種固定使用 'ymd' (年月日) 順序外, 前兩種預設是使用 'mdy’ 順序。不過我們可以針對每個登入使用者設定不同的順序 ('dmy' 或 'ymd'), 方法是更改其所使用的語言 (Language)。 SQL Server 中文版預設的語言是繁體中文 (Traditional Chinese), 順序是 'ymd'。若要更改使用者預設的語言, 可在 SQL Server Management Studio 中的 SQL Server 名稱上按右鈕, 執行『屬性』 命令後再切換到進階頁面：

更改年月日的順序

更改年月日的順序 如果想針對個別帳戶更改預設語言的設定, 請如下操作：

更改年月日的順序

更改年月日的順序 不過要注意, 以上設定是針對 T-SQL 語言所做的, 可適用於任何 T-SQL 敘述。 但若您是在 SQL Server Management Studio 中編輯資料, 那麼所使用的格式是依照 Windows 的控制台 / 地區及語言選項中的日期時間格式。

統一的日期表示法 當使用如 '01/02/03' 的日期時, 可能會因為當時使用了不同的語言而有不同的解釋。要避免因國別語言不同而造成的錯誤, 可用以下的方法： 如果是透過 ADO、OLE DB、或 ODBC 界面 來存取資料, 那麼可使用 ODBC 的標準表達方式：

統一的日期表示法 以其他方式存取資料時, 可使用年月日順序固定的 'yyyymmdd' 格式, 例如 '20051221', 這樣就不會因國別而有不同。 您也可用 CONVERT (data_type, expression, style) 函數, 將字串依指定格式轉換為日期資料, 例如： CONVERT 函數可以依照指定的日期格式, 在字串及日期資料間相互轉換, 其 style 參數所代表的格式如下：

統一的日期表示法

統一的日期表示法 上面 100 以下的 style 值是使用 'yy' 格式, 若加 100 則改用 'yyyy' 格式。但 0/100、9/109、13/113、20/120、21/121 一律使用 'yyyy' 格式。

10-5 隱含式型別轉換 當不同型別的資料做運算時, 系統會先將之轉換為相同的型別後才進行運算, 並將運算結果以轉換後的型別傳回。例如一個 smallint 的資料與 int 的資料相加, 那麼 smallint 的資料會先自動轉換成 int 型別, 然後再相加, 因此結果為 int 型別。 型別轉換的兩種類型 轉換型別的優先順序 型別轉換的限制

型別轉換的兩種類型 資料型別的轉換分成兩種類型, 像剛才 smallint 和 int 的資料進行運算, SQL Server 會自動將 smallint 資料轉換成 int 型別, 這樣的 "自動型別轉換" 就稱為隱含式型別轉換 (Implicit conversion)；而需由我們主動以 CAST 或 CONVERT 函數來轉換型別時, 則稱為強迫式型別轉換 (Explicit conversion)。

轉換型別的優先順序 當發生隱含式型別轉換時, SQL Server 會儘量將資料都轉換成可以容納較多資料的型別, 例如前述的 smallint 會轉換為 int 型別。 那麼各種不同型別間到底是如何轉換呢？其實資料型別也是有轉換的優先順序, 當兩筆資料要做運算時, 系統會將優先順序較低的型別轉換成優先順序較高的型別。

轉換型別的優先順序 資料型別的優先順序如下表所示：

型別轉換的限制 當然, 並不是每種型別都可以相互轉換的, 例如 nchar 型別是無法轉換成 image 的；而有些型別只能用強迫式型別轉換而不允許隱含式型別轉換, 例如 nchar 轉換成 binary, 或 text 轉換成 char 時。 另外, 有些資料在轉換型別時會損失一些精確度, 例如 3422.567 轉換為 int 時會變成 3422, 小數部份會被捨去。如果只是小數位數縮減的轉換, 則會做四捨五入, 例如 CAST(23.359289 AS MONEY) 的結果為 $23.3593。

10-6 T-SQL 的運算子 運算子是用來 "運算" 或 "判斷" 陳述式的值, 例如：+ (加)、- (減)、* (乘)、/ (除)、AND、OR、> (大於)、= (等於)、< (小於) ... 等等。T-SQL 的運算子共分為 7 類類, 底下我們就分類為各位詳細介紹。 指定運算子 ● 算數運算子 比較運算子 ● 邏輯運算子 位元運算子 ● 字串連結運算子 單一運算元運算子

指定運算子 指定運算子 (Assignment operator) 只有 1 個, 那就是 = (等號), 用來將數值或字串等資料指定給欄位或變數。例如：

指定運算子 在下例中, 我們使用 '=' 將數值指定給變數 (變數要以 @ 開頭)：

算數運算子 算數運算子 (Arithmetic operators) 包括 + (加)、- (減)、* (乘)、/ (除) 與 % (整數相除的餘數), 用來做為數值或日期的運算之用, 例如：

算數運算子 另外, 日期資料也可以與數值做加減運算, 其意義為日期加幾天或減幾天, 例如：

比較運算子 比較運算子 (Comparison operators) 用來比較數字的大小, 或是字串的差異。包括以下 9 種：

比較運算子

邏輯運算子 邏輯運算子 (Logical operators) 用來判斷條件為 True 或 False, 總共有 10 個運算子, 其中的 ALL、ANY、SOME、EXISTS、以及 IN 在前面介紹子查詢時已說明過了, 底下再介紹剩下的 5 個, 以及 IN 的補充說明。

邏輯運算子 AND、OR：AND (且) 與 OR (或) 運算子是做為兩個陳述式的邏輯判斷之用, 例如：

邏輯運算子 BETWEEN：BETWEEN 運算子表示在兩者之間, 因此只要是在給定條件之間的資料都符合要求。例如：

邏輯運算子 IN：IN 運算子用來判斷給定的值是否在指定的項目列表或是子查詢中, 其中子查詢的部份前面已經介紹過了, 下面範例則示範有關項目列表的使用方式：

邏輯運算子 LIKE：LIKE 運算子是用指定的字串來找尋記錄, 例如要尋找內容中有 'SQL'這個字的記錄：

邏輯運算子

邏輯運算子 NOT：NOT 運算子可將邏輯運算元的值反向, 亦即原來 True 變成 False, 而 False 變成 True。NOT 也可以與 EXISTS、LIKE、BETWEEN、IN 合用, 以產生相反的結果。

位元運算子 位元運算子 (Bitwise operators) 包括 & (AND)、| (OR) 與 ^ (Exclusive OR) 三種, 用來對位元進行邏輯運算。 & ：當此運算子前後的兩個運算元都為 1 的時候, 結果為 1。只要有一個不是 1, 則結果為 0。 | ：此運算子前後的兩個運算元只要有一個是 1, 則結果就是 1, 只有當兩個都是 0 的時候才會是 0。

位元運算子 ^：此為互斥運算子, 當兩個運算元的值不一樣的時候才會是 1, 否則為 0。

字串連結運算子 字串連結運算子 (String concatenation operator) 是用來連結字串用的, 表示符號為 +。字串的資料型別必須是 char、varchar、或 text, 若有其它資料型別的資料要與字串相加, 則必須要能轉換為字串型別才行。

單一運算元運算子 單一運算元運算子 (Unary operators) 只作用於單一的運算元, 總共有 3 種：+ (正號)、- (負號) 與 ~ (bitwise not, 補數)。其中補數是指位元的互補數字, 例如 1001 的補數是 0110。請看下面的範例：

10-7 運算子的優先順序 當使用多個運算子來組成運算式時, 優先順序較高的運算子會優先做運算。例如 “3+2*2” 的結果為 3+4=7, 而不是 5*2=10。 如果希望某部份能夠優先運算, 那麼可用小括號括起來, 如果有多層的小括號, 則在內層的算式會先做運算, 例如 "3*(6/(4-2))" 的結果為 3*(6/2)=3*3=9。

運算子的優先順序 下表我們將 T-SQL 運算子的優先順序, 由高到低列出：

運算子的優先順序 注意, 在上表中同一層的運算子其優先順序相同, 例如 *、/、和 %。如果運算式中各運算子的優先順序相同, 則會以 "由左到右" 的順序進行運算。例如 "4-3+1" 的結果為 1+1=2。

10-8 處理欄位中的 NULL 值 NULL 代表的是一個未知的值, 假如我們在查詢資料時, 想要找出某欄位值是 NULL 的記錄該怎麼做呢？又 NULL 值可以做運算嗎？請看底下的介紹吧！ NULL 值的運算 ISNULL( ) 函數 檢查是否為 NULL 值

NULL 值的運算 NULL 代表一個不知道的值, 因此任二個 NULL 值都不會相等, 因為其內的值是不確定的；同理, 像是 "5 > NULL"、"NULL <= 100" 的運算式都有問題。另外, 任何資料與 NULL 值做運算, 例如 "20 + NULL", 其值仍然是 NULL。 如果希望 NULL 值可以用等號做比較, 那麼必須如下將系統選項 ANSI_NULLS 設為 OFF 才行 (預設是 ON), 否則比較的結果永遠是 FALSE。請看下面的範例：

NULL 值的運算 當 ANSI_NULLS 選項設為 OFF 時, 除了相等 (=) 的比較外, 其他用 NULL 值做大於、小於...等的比較時, 其結果均為 False。

ISNULL( ) 函數 ISNULL( ) 函數可用來在輸出時替換 NULL 值, 其語法如下： check_expression 是要接受檢查的運算式, 檢查後, 若其值為 NULL, 就傳回 replacement_value；若不是 NULL, 則將 check_expression 的原值傳回。下面來看個範例：

ISNULL( ) 函數

ISNULL( ) 函數 請注意, replace_expression 的型別必須和 check_expression 相同才行！這就是為什麼在上例中要先用 CAST 函數轉換主管編號為字串型別的原因了。

檢查是否為 NULL 值 我們可以用 IS NULL 或 IS NOT NULL 來判斷 NULL 值, 例如：

檢查是否為 NULL 值 通常在資料庫中都會儘量避免過度使用 NULL 值, 以免在查詢時發生錯誤而不自覺；例如彙總函數 (SUM、AVG...) 會自動略過 NULL 值不做計算。 如果您想要將全部的 NULL 值都設為一個固定的正常值, 可以如下操作：

檢查是否為 NULL 值

10-9 使用 APPLY 運算子在查詢時同時呼叫函數進行運算 例如我們可以查詢資料庫得到一份書籍名稱與價格的清單, 但如果想要查得價格打 8 折後的清單, 便可以利用 APPLY 運算子, 在查詢的同時呼叫一個自訂函數將價格乘以 0.8, 然後 APPLY 運算子會結合原先查詢得到的資料與新運算出來的結果, 直接顯示出一份價格打折後的清單。

使用 APPLY 運算子在查詢時同時呼叫函數進行運算 假設目前書籍的名稱、售價等資料清單如下：

使用 APPLY 運算子在查詢時同時呼叫函數進行運算 若我們想要得到一份打折後 (例如 8 折) 的書籍售價, 一般會使用的方法如下：

使用 APPLY 運算子在查詢時同時呼叫函數進行運算 但是萬一您要處理的結果需要很複雜的計算, 通常我們會將此計算式設計成函數的方式以方便呼叫。 為了方便說明, 筆者仍以同樣的例子來示範 (如果是簡單的查詢, 使用上面的方法即可)。 因為會用到自訂函數, 所以請您先如下建立計算優惠的函數：

使用 APPLY 運算子在查詢時同時呼叫函數進行運算

使用 APPLY 運算子在查詢時同時呼叫函數進行運算 查詢的結果如下：

10-10 排序函數：ROW_NUMBER( )、RANK() 與 DENSE_RANK() SQL Server 2005 新增了數個排序函數：ROW_NUMBER()、RANK() 與 DENSE_RANK(), 讓您可以依照各種不同的需求, 針對資料表的欄位進行排序, 或是指定查詢的範圍, 本節將為您說明如何使用這些函數。 針對資料表的欄位進行排序 指定查詢範圍

針對資料表的欄位進行排序 ROW_NUMBER() 可以依照指定的欄位將所有記錄進行排序, 然後再依照順利為每一筆記錄給定一個序號。 例如我們可以如下列出書籍價格的排名, 它遇到相同的價格時會依其他的依據來決定排名, 因此名次均不會相同：

針對資料表的欄位進行排序 輸出結果如下：

針對資料表的欄位進行排序 而 RANK() 函數遇到相同的數值會給相同的排名, 其後的排名則會跳過。 例如有三個第 2 名時, 就不會有第 3 及第 4 名, 它會從第 5 名開始。RANK() 的用法與 ROW_UNMBER() 是類似的：

針對資料表的欄位進行排序 其輸出結果如下：

針對資料表的欄位進行排序 如果您不想如上例 "2、2、2、5" 自動跳過後面排名, 而想要使用 "2、2、2、3" 這樣的排名方式, 則只要將上例中的 RANK() 函數改為 DENSE_RANK() 函數即可：

針對資料表的欄位進行排序

指定查詢範圍 排序函數除了可以將所有記錄進行排序, 還能夠指定查詢第 M 筆到第 N 筆的記錄, 例如查詢價格排名第 5 到 8 名的書籍。 過去在舊版的 SQL Server 要作上述查詢時, 必須使用第 9 章介紹的 TOP 敘述先查出資料的前 8 筆, 然後將這些記錄反向排序後再查詢前 4 筆。當資料筆數不多倒還無妨, 若是資料一多, 查詢起來既耗時又浪費系統資源。

指定查詢範圍 不過現在只要使用排序函數, 配合 CTE (Common Table Expression, 一般資料表運算式) 語法, 便可以直接查詢某一個範圍內的記錄了。 下面的範例是查詢書籍資料表內依照價格排名的第 5 至 8 筆記錄：

指定查詢範圍

10-11 使用 PIVOT 與 UNPIVOT 將直式資料表轉為橫式 PIVOT 可以將直式的資料表轉為橫式的資料表, 而 UNPIVOT 則相反。下面就是使用 PIVOT 的效果：

建立不含主索引鍵的資料表 隨後將說明如何使用 PIVOT 進行上面例子的轉換。 為了方便解說, 筆者先在練習 02 資料庫中的出貨記錄資料表最後面加入一筆訂單, 其書名及客戶欄位的資料與第 2 筆相同, 僅數量不同：

建立不含主索引鍵的資料表 所以目前出貨記錄資料表內的所有記錄如下：

建立不含主索引鍵的資料表 筆者想要使用 PIVOT 運算子將此表格轉成橫式, 並計算每個客戶對每本書訂購的總數量：

建立不含主索引鍵的資料表 不過筆者測試發現若資料表含有如序號這類的主索引鍵欄位時, 在使用 PIVOT 運算子時會發生無法將相同的資料加總的情形。 因此請如下建立一個不含主索引鍵的資料表, 並將原本的資料匯入：

建立不含主索引鍵的資料表

PIVOT 的語法與使用方法 PIVOT 運算子使用的語法如下：

PIVOT 的語法與使用方法 其中 column_list 為您要轉為橫式資料表的欄位, 您必須自行列出。 例如本例就是要將原本客戶名稱欄位的天天書局與大雄書局轉為橫式的資料表欄位。而aggregate_function() 您可以換為想要使用的函數, 例如 SUM() 加總函數。 使用 PIVOT 轉換剛才新建 My_Table1 資料表內容的方式如下：

PIVOT 的語法與使用方法 其輸出結果如下： 以上筆者使用 SUM( ) 函數將單一客戶所訂購同樣書名的產品加總, 這樣您就可以知道每個客戶對於單一產品訂購的總數量了。

UNPIVOT 的語法與使用方法 UNPIVOT 運算子的用法與 PIVOT 類似, 它的語法如下：

UNPIVOT 的語法與使用方法 首先我們建一個新的資料表, 並將剛才使用 PIVOT 運算子查詢的結果匯入：

UNPIVOT 的語法與使用方法 這時我們可以如下使用 UNPIVOT 運算子將這個資料表再轉回原來的格式 (加總後的結果)：

UNPIVOT 的語法與使用方法 其輸出結果如下： 所以使用 PIVOT 與 UNPIVOT 運算子, 便可以方便的轉換不含主索引鍵資料表 (例如由 Excel 試算表所匯入) 的格式。