T-SQL的基礎：超越基礎6級：使用CASE運算式和IIF函數

標籤：允許   order   實現   ssi   value   str   範圍   false   null   

                                                                                                                                                          T-SQL的基礎：超越基礎6級：使用CASE運算式和IIF函數
                                                                                                                                                       Gregory Larsen，2016/04/20（第一版：2014/04/09）

該系列
本文是“Stairway系列：T-SQL的基石：超越基礎”的一部分

從他的Stairway到T-SQL DML之後，Gregory Larsen涵蓋了T-SQL語言的更多進階方面，例如子查詢。

在某些情況下，您需要編寫一個能夠基於另一個運算式的評估返回不同TSQL運算式的單個TSQL語句。當您需要這種功能時，您可以使用CASE運算式或IIF功能來滿足此要求。在本文中，我將回顧CASE和IIF文法，並向您展示CASE運算式和IIF函數的樣本。

理解CASE運算式
Transact-SQL CASE運算式允許您在TSQL代碼中放置條件邏輯。此條件邏輯為您提供了一種方法，可以根據條件邏輯的TRUE或FALSE評估，在您的TSQL語句中放置不同的代碼塊。您可以將多個條件運算式放在一個CASE運算式中。當您的CASE子句中有多個條件運算式時，第一個計算結果為TRUE的運算式將成為由您的TSQL語句計算的代碼塊。為了更好地理解CASE運算式的工作原理，我將回顧CASE運算式的文法，然後通過一些不同的例子。

CASE運算式文法
CASE運算式有兩種不同的格式：Simple和Searched。這些類型中的每一個都有一個稍微不同的格式，1所示。

簡單的CASE運算式：

CASE input_expression
     WHEN when_expression THEN result_expression [... n]
     [ELSE else_result_expression]
結束

搜尋CASE運算式：

案例
     WHEN布林運算式THEN result_expression [... n]
     [ELSE else_result_expression]
結束
圖1：CASE運算式文法

通過查看圖1中CASE運算式的兩種不同格式，可以看到每種格式如何提供一種不同的方法來識別決定CASE運算式結果的多個運算式之一。對於兩種類型的CASE，都會為每個WHEN子句執行布爾測試。使用簡單的CASE運算式，布爾檢驗的左邊出現在CASE字的後面，稱為“輸入運算式”，布爾檢驗的右邊緊跟在WHEN之後，稱為“when運算式”。對於簡單CASE運算式，“input_expression”和“when_expression”之間的運算子總是等於運算子。而搜尋的CASE運算式中，每個WHEN子句將包含一個“Boolean_expression”。這個“布林運算式”可以是一個具有單一運算子的簡單布林運算式，也可以是具有許多不同條件的複雜布林運算式。另外，搜尋到的CASE運算式可以使用全套布林運算子。

無論使用哪種CASE格式，每個WHEN子句按其出現的順序進行比較。 CASE運算式的結果將基於評估為TRUE的第一個WHEN子句。如果沒有WHEN子句求值為TRUE，則返回ELSE運算式。當ELSE子句被省略，WHEN子句的計算結果為TRUE時，則返回NULL值。

樣本的樣本資料
為了讓一個表使用CASE運算式進行示範，我將使用清單1中的指令碼來建立一個名為MyOrder的樣本表。如果您想跟隨我的樣本並在您的SQL Server執行個體上運行它們，您可以在您選擇的資料庫中建立此表。

CREATE TABLE MyOrder（
ID int身份，
OrderDT日期，
OrderAmt decimal（10,2），
Layaway char（1））;
INSERT到MyOrder值
（‘12 -11-2012‘，10.59，NULL），
（‘10 -11-2012‘，200.45，‘Y‘），
（‘02-17-2014‘，8.65，NULL），
（‘01 -01-2014‘，75.38，NULL），
（‘07-10-2013‘，123.54，NULL），
（‘08 -23-2009‘，99.99，NULL），
（‘10-08-2013‘，350.17，‘N‘），
（‘04-05-2010‘，180.76，NULL），
（‘03 -27-2011‘，1.49，NULL）;
清單1：建立樣本表MyOrder

在WHEN和ELSE運算式中使用簡單的CASE運算式
為了示範簡單的CASE運算式格式如何工作，讓我運行清單2中的代碼。

SELECT YEAR（OrderDT）AS OrderYear，
       CASE年（OrderDT）
當2014年那麼‘第一年‘
當2013年那麼‘第二年‘
當2012年那麼‘3年‘
ELSE“第四年及以後”年份類型
FROM MyOrder;
清單2：使用ELSE運算式的簡單CASE運算式

讓我先來談談為什麼這是一個簡單的CASE運算式。如果您查看清單2中的代碼，您可以看到在CASE之後指定了“YEAR（OrderDT）”這個運算式，然後我從三個不同的WHEN運算式中選擇了三個不同的WHEN運算式，從2014開始。因為我指定CASE和第一個WHEN關鍵字之間的運算式，這告訴SQL Server這是一個簡單的CASE運算式。

當我的簡單CASE運算式被評估時，它使用“YEAR（OrderDate）”值和不同的WHEN運算式之間的等號運算子（“=”）。因此，清單1中的代碼將顯示OrderDT年值為“2014”的行的YearType列的“Year 1”，或者OrderDT年為“2013”??的行顯示“Year 2”將顯示OrderDT年份為“2012”的行的“Year 3”。如果OrderDT的年份與WHEN運算式中的任何一個都不匹配，則ELSE條件將顯示“第4年及以後”。

當我運行清單2中的代碼時，得到了Result 1中顯示的輸出。

OrderYear YearType
----------- -----------------
2012年3
2012年3
2014年1
2014年1
2013年2
2009年4年及以後
2013年2
2010年4年及以後
2011年4年及以後
結果1：運行清單2時的結果

使用一個沒有ELSE運算式的簡單CASE運算式
讓我運行清單3中的代碼，它將顯示簡單CASE運算式沒有ELSE子句時會發生什麼情況。

SELECT YEAR（OrderDT）AS OrderYear，
       CASE年（OrderDT）
當2014年那麼‘第一年‘
當2013年那麼‘第二年‘
當2012年那麼‘年3‘結束年份類型
FROM MyOrder;
清單3：沒有ELSE子句的簡單CASE運算式

清單3中的代碼就像清單2中的代碼一樣，但沒有ELSE子句。當我運行清單3中的代碼時，它會產生結果2中顯示的結果。

OrderYear YearType
----------- --------
2012年3
2012年3
2014年1
2014年1
2013年2
2009 NULL
2013年2
2010 NULL
2011 NULL
結果2：運行清單3時的結果

通過查看Result 2中的輸出，可以看到MyOrder表中的OrderDT的年份不符合WHEN子句條件時，SQL Server顯示該行的YearType值為“NULL”。

使用搜尋CASE運算式
在簡單的CASE運算式中，WHEN運算式是基於等式運算子來評估的。通過搜尋CASE運算式，您可以使用其他運算子，CASE運算式文法稍有不同。為了示範這個，我們來看看清單4中的代碼。

SELECT YEAR（OrderDT）AS OrderYear，
       案例
當年（OrderDT）= 2014那麼“第一年”
當年（OrderDT）= 2013 THEN‘2年‘
年（OrderDT）= 2012 THEN‘3年‘
年（OrderDT）<2012 THEN‘4年及以後‘
                       END AS YearType
FROM MyOrder;
清單4：搜尋CASE運算式

如果查看清單4中的代碼，則可以看到WHEN子句緊跟在CASE子句之後，兩個子句之間沒有文本。這告訴SQL Server這個搜尋的CASE運算式。還要注意每個WHEN子句後面的布林運算式。正如你所看到的，並不是所有的布林運算式都使用了相等運算子，最後一個WHEN運算式使用了小於（“<”）的運算子。清單4中的CASE運算式在邏輯上與清單2中的CASE運算式相同。因此，當我運行清單4中的代碼時，它將產生與Result 1中所示相同的結果。

如果多個WHEN運算式求值為TRUE，那麼返回什麼運算式？
在單個CASE運算式中，可能會出現不同的WHEN運算式計算為TRUE的情況。發生這種情況時，SQL Server將返回與計算結果為true的第一個WHEN運算式關聯的結果運算式。因此，如果多個WHEN子句的值為TRUE，則WHEN子句的順序將控制從CASE運算式返回的結果。

為了示範這一點，讓我們使用CASE運算式，當OrderAmt在$ 200範圍內時顯示“200美元訂單”，當OrderAmt在$ 100範圍內時顯示“100美元訂單”，當OrderAmt小於100美元時顯示“<100美元訂單”當OrderAmt不屬於這些類別時，則將訂單歸類為“300美元及以上訂單”。讓我們回顧清單5中的代碼，以示範當嘗試將訂單分類到其中一個OrderAmt_Category值時，多個WHEN運算式求值為TRUE時會發生什麼情況。

SELECT OrderAmt，
       案例
當訂單金額<300那麼‘200美元訂單‘
當OrderAmt <200那麼‘100美元訂單‘
當OrderAmt <100 THEN‘<100美元訂單‘
ELSE‘300美元及以上訂單‘
END作為OrderAmt_Category
FROM MyOrder;
清單5：多個WHEN運算式求值為TRUE的樣本

當我運行清單5中的代碼時，v得到Result 3中的輸出。

OrderAmt OrderAmt_Category
--------------------------------------- ----------- ---------------
10.59 200美元訂單
200.45 200美元的訂單
8.65 200美元訂單
75.38 200美元訂單
123.54 200美元的訂單
99.99 200美元的訂單
350.17 300美元及以上訂單
180.76 200美元訂單
1.49 200美元的訂單
結果3：運行清單5時的結果

通過查看結果3中的結果，您可以看到每個訂單都被報告為200或300以上的訂單，而且我們知道這是不正確的。發生這種情況是因為我只使用小於（“<”）運算子來簡化歸類在我的CASE運算式中導致多個WHEN運算式求值為TRUE的Orders。 WHEN子句的排序不允許返回正確的運算式。

通過重新排序我的WHEN子句，我可以得到我想要的結果。清單6中的代碼與清單5中的代碼相同，但是我重新命令了WHEN子句來正確地分類我的訂單。

SELECT OrderAmt，
       案例
當OrderAmt <100 THEN‘<100美元訂單‘
當OrderAmt <200那麼‘100美元訂單‘
當訂單金額<300那麼‘200美元訂單‘
ELSE‘300美元及以上訂單‘
END作為OrderAmt_Category
FROM MyOrder;
清單6：與清單5類似的代碼，但WHEN子句的順序不同

當我運行清單5中的代碼時，得到Result 4中的輸出。

OrderAmt OrderAmt_Category
--------------------------------------- ----------- ---------------
10.59 <100美元訂單
200.45 200美元的訂單
8.65 <100美元訂單
75.38 <100美元訂單
123.54 100美元訂單
99.99 <100美元的訂單
350.17 300美元及以上訂單
180.76 100美元訂單
1.49 <100美元的訂單
結果4：運行清單6時的結果

通過查看結果4中的輸出，您可以看到，通過更改WHEN運算式的順序，我得到了每個訂單的正確結果。

嵌套CASE運算式
有時您可能需要進行額外的測試，以使用CASE運算式進一步對資料進行分類。發生這種情況時，可以使用嵌套的CASE運算式。清單7中的代碼顯示了一個嵌套CASE運算式的例子，以進一步對MyOrder表中的訂單進行分類，以確定當訂單超過200美元時是否使用Layaway值購買訂單。

SELECT OrderAmt，
       案例
當OrderAmt <100 THEN‘<100美元訂單‘
當OrderAmt <200那麼‘100美元訂單‘
當OrderAmt <300那麼
案例
當Layaway =‘N‘
那麼‘沒有Layaway的200美元訂單‘
ELSE‘200美元訂單與LAYWAY‘結束
其他
案例
當Layaway =‘N‘
那麼‘沒有Layaway的300美元訂單‘
ELSE‘300美元訂單與LAYWAY‘結束
END作為OrderAmt_Category
FROM MyOrder;
清單7：嵌套CASE語句

清單7中的代碼與清單6中的代碼類似。唯一的區別是我添加了一個額外的CASE運算式，以查看MyOrder表中的訂單是否使用Layaway選項購買的，該選項僅在超過200美元的購買時才被允許。請注意，嵌套CASE運算式時，SQL Server只允許有多達10個嵌套層級。

其他可以使用CASE運算式的地方
到目前為止，我的所有樣本都使用CASE運算式將CASE運算式放置在TSQL SELECT語句的挑選清單中，以建立結果字串。您也可以在UPDATE，DELETE和SET語句中使用CASE運算式。此外，CASE運算式可以與IN，WHERE，ORDER BY和HAVING子句結合使用。在清單8中，我使用了一個表達WHERE子句的CASE。

選擇 *
從MyOrder
WHERE CASE年（OrderDT）
當2014年那麼‘第一年‘
當2013年那麼‘第二年‘
當2012年那麼‘3年‘
ELSE“第四年及以後”END =“第一年”;
清單8：在WHERE子句中使用CASE運算式

在清單8中，我只想從“MyOrder”表中返回“Year 1”中的行的訂單。為了實現這個，我在WHERE子句中放置了與清單2中使用的相同的CASE運算式。我使用CASE運算式作為WHERE條件的左邊部分，因此它會根據OrderDT列產生不同的“Year ...”字串。然後，我測試了從CASE運算式產生的字串，看它是否等於“Year 1”的值，當它是從MyOrder表返回的時候。請記住，如果還有其他更好的方法，例如使用YEAR函數選擇給定年份的行，我不建議使用CASE運算式來使用類似“Year 1”的sting從日期列中選擇日期。我只在這裡做了示範如何在WHERE子句中使用CASE語句。

使用IIF函數快速切換CASE運算式
隨著SQL Server 2012的推出，微軟添加了IIF功能。 IIF功能可被視為CASE聲明的捷徑。在圖2中，您可以找到IIF函數的文法。

IIF（布林運算式，true_value，false_value）
圖2：IIF函數的文法

“布林運算式”是一個有效布林運算式，等同於TRUE或FALSE。當布林運算式等同於TRUE值時，執行“true_value”運算式。如果布林運算式等於FALSE，則執行“false_value”。就像CASE運算式一樣，IIF函數可以嵌套到10個層級。

使用IIF的例子
為了示範如何使用IIF函數替換CASE運算式，讓我們回顧一下清單9中使用搜尋的CASE運算式的代碼。

SELECT OrderAmt，
       案例
當OrderAmt> 200 THEN‘高$訂單‘
ELSE‘Low $ Order‘END AS OrderType
FROM MyOrder;
清單9：簡單CASE運算式樣本

清單9中的代碼只有一個WHEN運算式，用於確定OrderAmt是高位還是低位。如果WHEN運算式“OrderAMT> 200”評估為TRUE，則OrderType值設定為“High $ Order”。如果WHEN運算式的計算結果為FALSE，則為OrderType值設定“Low $ Order”。

使用IIF函數而不是CASE運算式的重寫代碼可以在清單10中找到。

SELECT OrderAmt，
IIF（OrderAmt> 200，
‘高$訂單‘，
‘低$訂單‘）AS OrderType
FROM MyOrder;
清單10：使用IIF函數的例子

通過查看清單10，您可以看到為什麼IIF函數被認為是CASE運算式的簡寫版本。用“IIF（”字串，“THEN”子句替換為逗號，“ELSE”子句替換為逗號，“END”替換為“）”替換CASE一詞。當布林運算式“OrderAmt> 200”為TRUE時，顯示值“High $ Order”。當布林運算式“OrderAmt> 200”被評估為FALSE時，則顯示“Low $ Order”。如果運行清單9和10中的代碼，您將看到它們都產生完全相同的輸出。

嵌套IIF功能的樣本
就像CASE運算式SQL Server允許你嵌套IIF函數。在清單11中是嵌套IIF函數的一個例子。

SELECT OrderAmt，
       IIF（OrderAmt <100，
‘<100美元訂單‘，
（IIF（OrderAmt <200，
‘100美元訂單‘，
（IIF（OrderAmt <300，
（IIF（Layaway =‘N‘，
“沒有LAYWAY的200美元訂單”，
“與Layaway的200美元訂單”
）
）
（IIF（Layaway =‘N‘，
“沒有Layaway的300美元訂單”，
‘與Layaway 300美元訂單‘
）
）
）
）
）
）
）AS OrderAmt_Category
FROM MyOrder;
清單11：嵌套IIF函數的例子

在這個例子中，你可以看到我已經多次使用了IIF函數。每個額外的一個用於IIF功能的“真實值”或“假值”。清單11中的代碼與清單7中使用嵌套CASE運算式的代碼相同。

限制
與大多數TSQL功能一樣，這是有限制的。以下是有關CASE和IIF結構的一些限制。

CASE運算式限制：

CASE運算式中最多隻能有10層嵌套。
CASE運算式不能用來控制TSQL語句的執行流程。
IIF功能限制：

IIF條款最多隻能有10層嵌套。
概要
CASE運算式和IIF函數允許您將運算式邏輯放置在TSQL代碼中，這將根據運算式的評估結果更改代碼的結果。通過使用IIF函數和CASE運算式支援的比較運算式，您可以根據比較運算式計算結果為TRUE還是FALSE來執行不同的代碼塊。 CASE運算式和IIF函數為您提供者控制，以滿足您可能不具備的業務需求。

問題和答案
在本節中，您可以通過回答以下問題來查看使用CASE和IIF構造理解的情況。

問題1：
CASE運算式有兩種不同的文法變體：Simple和Searched。以下哪兩條語句最好地描述了簡單搜尋CASE運算式（選擇兩個）之間的區別。

簡單CASE文法僅支援相等運算子，而搜尋CASE文法支援多個運算子
簡單CASE文法支援多個運算子，而Searled CASE文法僅支援相等運算子
簡單CASE文法在WHEN子句之後指定了其布林運算式，而搜尋CASE文法在CASE語句之後有布林運算式的左側，在WHEN子句之後布林運算式的右側有布林運算式的右側。
簡單CASE文法在CASE語句後面布林運算式的左側，在WHEN子句後面布林運算式的右側，而搜尋CASE運算式在WHEN子句後面有布林運算式
問題2：
如果CASE運算式有多個THEN / ELSE子句執行的WHEN子句計算為TRUE，

執行最後一個計算為TRUE的WHEN子句的THEN運算式。
執行第一個WHEN子句的THEN運算式，其值為TRUE。
執行所有THEN運算式的WHEN子句，其值為TRUE。
ELSE運算式被執行
問題3：
CASE運算式或IIF函數有多少個嵌套層級？

8
10
16
32
回答：
問題1：
答案是a和d。一個簡單的CASE語句只能使用相等運算子，而Searled CASE運算式可以處理多個運算子以及複雜的布林運算式。另外，簡單CASE文法在單詞CASE之後有相等操作符的左邊部分，在WHEN之後有相等操作符的右邊部分。 Searled CASE運算式必須在WHEN子句之後完成布爾運算（左邊部分，運算子，右邊部分）

問題2：
正確答案是b。如果多個WHEN子句評估為TRUE，則SQL Server僅執行第一個WHEN子句的THEN部分，其值為TRUE。所有其他THEN子句的任何其他WHEN子句被評估為TRUE被跳過。

問題3：
正確答案是b。 CASE運算式和IIF函數最多隻支援10個嵌套層次。

 
本文是T-SQL的基礎：超越基礎的階梯

T-SQL的基礎：超越基礎6級：使用CASE運算式和IIF函數