t - sql的階梯:超越基礎水平6:使用表示式和IIF函式
通過格雷戈裡·拉森,2016/04/20(第一次出版:2014/04/09)
該系列
本文是樓梯系列的一部分:樓梯t - sql:除了基礎知識
從他的t - sql DML樓梯後,格雷戈裡·拉森涵蓋了更高階的子查詢等方面的t - sql語言。
有時,你需要寫一個TSQL語句能夠返回不同TSQL表示式基於另一個表示式的評價。 當你需要這種功能可以使用表示式或IIF函式來達到這個要求。 在本文中,我將回顧這一案件,IIF語法和顯示你如何表達和IIF函式的例子。
理解這樣表達
transact - sql例表示式允許你在TSQL放置條件邏輯程式碼。 這條件邏輯提供了一種不同的可執行程式碼塊在你TSQL語句根據條件邏輯或真或假的評價。 您可以將多個條件表示式的一個表示式。 當你在條款中,有多個條件表示式值為TRUE的第一個表示式將評估你的TSQL語句的程式碼塊。 為了更好地理解如何表達作品我將審查情況下表達的語法,然後經過許多不同的例子。
情況下表示式語法
這樣表達有兩個不同的格式:簡單搜尋。 每一種型別都有一個稍微不同的格式如圖1所示。
簡單的案例表達:案例input_expression
當when_expression result_expression[… n]
(其他else_result_expression)
結束搜尋案例表達:情況下
當Boolean_expression result_expression[… n]
(其他else_result_expression)
結束
圖1:表示式語法
通過回顧情況下表達的兩種不同的格式在圖1中可以看到每個格式提供了一種不同的方式來識別一個多個表示式,確定情況下表達的結果。 有兩種型別的情況下,一個布林測試時為每個條款執行。 對這個簡單的例子表達左手邊的布林測試後出現的話,叫做“input_expression”,和右手站後的布林測試是正確的單詞時,稱為“當表示式”。 對這個簡單的例子表達之間的運算子“input_expression”和“when_expression”總是相等操作符。 而搜尋情況下表達每個當條款將包含一個“Boolean_expression”。 這種“Boolean_expression”可以是一個簡單的布林表示式用一個運營商,與許多不同的條件或一個複雜的布林表示式。 此外,搜尋情況下表示式可以使用布林操作符的全套。
不管這種情況下使用格式,每個條款的順序比較的時候出現。 案件的結果表示式時將根據第一條款評估為TRUE。 如果沒有當其他子句的計算結果為真時,則返回表示式。 時省略其他條款和條款評估為TRUE,然後返回一個NULL值。
樣本資料的例子
為了有一個表來演示使用情況下表達我將使用清單1中的指令碼建立一個示例表命名MyOrder。 如果你想跟隨我的例子,在您的SQL伺服器例項上執行它們可以建立這個表在資料庫中。
建立表MyOrder(
int ID身份,
OrderDT日期,
OrderAmt小數(10,2),
分期預付char(1));
插入MyOrder值
(' 12-11-2012 ',10.59,NULL),
200.45(' 10-11-2012 ',' 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),
350.17(' 10-08-2013 ',' N '),
(' 04-05-2010 ',180.76,NULL),
(' 03-27-2011 ',1.49,NULL);
清單1:MyOrder建立示例表
用一個簡單的案例表達和其他表情
演示如何簡單的案例表達格式工作讓我執行清單2中的程式碼。
選擇一年(OrderDT)OrderYear,
例年(OrderDT)
當2014年“第一年”
當2013年之後的第二年
當2012年“三年級”
其他4年和超越YearType結束
從MyOrder;
清單2:簡單的表示式與其他表示式
讓我先談談為什麼這是一個簡單的表示式。 如果你回顧清單2中的程式碼後你可以看到我指定表示式”這個詞(OrderDT)”,然後我跟著,通過三種不同當表示式指定每一個擁有不同的一年,從2014年開始。 因為我指定的表示式之間的情況,第一個當關鍵字告訴SQL伺服器,這是一個簡單的表示式。
當我的簡單的情況下計算表示式它使用相等操作符(" = ")之間的“年(向資料庫)“價值和不同的表示式。 因此,清單1中的程式碼將顯示”第一年”YearType行與列OrderDT年的價值”2014年”,或者它將顯示“第二年“行了OrderDT年的“2013年”或它將顯示”三年級“行了OrderDT年的“2012”。 如果今年的OrderDT不匹配的任何表示式然後其他條件時將顯示“年4和超越”。
當我執行清單2中的程式碼1所示的輸出結果。
OrderYear YearType
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
2012年3
2012年3
2014年1
2014年1
2013年2
2009年及以後
2013年2
2010年及以後
2011年及以後
結果1:當執行清單2所示的結果
使用一個簡單的表示式沒有其他表示式
讓我執行清單3中的程式碼將顯示一個簡單的案例表達時會發生什麼沒有一個ELSE子句。
選擇一年(OrderDT)OrderYear,
例年(OrderDT)
當2014年“第一年”
當2013年之後的第二年
當2012年“三年級”作為YearType結束
從MyOrder;
清單3:簡單的案例表達沒有其他條款
清單3中的程式碼就像清單2中程式碼但沒有一個ELSE子句。 當我執行清單3中的程式碼產生結果2所示的結果。
OrderYear YearType
- - - - - - - - - - - - - - - - - - - - -
2012年3
2012年3
2014年1
2014年1
2013年2
2009年零
2013年2
2010年零
2011年零
結果2:當執行清單3的結果
通過評審的輸出結果2中可以看到,當今年的OrderDT在MyOrder表不符合條款條件時的任何SQL Server為YearType值顯示“零”這一行。
使用一個搜尋表示式
在簡單的情況下表達基於相等操作符表示式進行評估。 的搜尋表示式可以使用其他運營商,和表示式語法有點不同。 證明這個讓我們來看看清單4中的程式碼。
選擇一年(OrderDT)OrderYear,
情況下
當(OrderDT)= 2014年的第一年
當(OrderDT)= 2013年的第二年
當(OrderDT)= 2012年“三年級”
當一年(OrderDT)< 2012年4和超越的
作為YearType結束
從MyOrder;
清單4:搜尋表示式
如果你看看清單4中的程式碼時,你可以看到情況後直接遵循條款沒有文字之間的兩個條款。 這告訴SQL Server這個搜尋表示式。 還要注意每個當條款後的布林表示式。 正如你所看到的並不是所有的這些布林表示式使用相等操作符,最後當表示式使用小於(<)運算元。 清單4中的情況下表達邏輯與清單2中的表達情況。 因此當我執行清單4中的程式碼會產生相同的結果,結果1所示。
如果多個表示式返回當表示式計算為TRUE ?
可能會有不同的情況下當表示式計算為TRUE在單一情況下的表示式。 這時SQL伺服器將返回結果表示式與第一個當表示式計算為true。 因此當從句的順序將控制你會返回什麼結果你的案子表示式時如果多個條款評估為TRUE。
展示我們用這樣的表情來顯示”200美元的訂單“當OrderAmt在200美元的範圍之內。”100美元的訂單“當OrderAmt是100美元的範圍內“< 100美元的訂單“當OrderAmt小於100美元當一個OrderAmt不屬於任何的這些類別分類的順序為“300美元以上的訂單”。 讓我們回顧一下清單5中的程式碼演示當多個當表示式計算為TRUE時向其中一個訂單進行分類OrderAmt_Category值。
選擇OrderAmt,
情況下
當OrderAmt < 300 300美元訂單
當OrderAmt < 200 200美元訂單
當OrderAmt < 100 < 100美元訂單
其他的300美元以上的訂單
作為OrderAmt_Category結束
從MyOrder;
清單5:在多個例子當表示式計算為TRUE
當我執行清單5中的程式碼得到輸出結果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或200以上的訂單,我們知道這是不正確的。 這是因為我只用不到(“<”)操作符簡單地分類訂單導致多個當表示式計算為TRUE在我的例子中表達。 當從句的順序不允許返回正確的表示式。
通過重建創造條件條款時我可以得到我想要的結果。 清單6中的程式碼清單5是一樣的但我有重新定購商品當條款正確分類我的命令。
選擇OrderAmt,
情況下
當OrderAmt < 100 < 100美元訂單
當OrderAmt < 200 200美元訂單
當OrderAmt < 300 300美元訂單
其他的300美元以上的訂單
作為OrderAmt_Category結束
從MyOrder;
清單6:類似的程式碼如清單5,但當條款以不同的順序
當我執行清單5中的程式碼得到輸出結果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中,您可以看到,通過改變當表示式的順序為每個訂單我得到了正確的結果。
巢狀情況下表示式
偶爾你可能會需要額外的測試使用情況進一步分類資料的表示式。 當你可以用一個巢狀的情況下發生的表示式。 清單7中的程式碼顯示了一個示例的巢狀表示式進一步分類訂單MyOrder表來確定訂單購買使用分期預付當訂單價值超過200美元。
選擇OrderAmt,
情況下
當OrderAmt < 100 < 100美元訂單
當OrderAmt < 200 200美元訂單
當OrderAmt < 300
情況下
當禮物= ' N '
然後200美元訂單沒有禮物的
其他與分期預付200美元訂單結束
其他的
情況下
當禮物= ' N '
然後300美元訂單沒有禮物的
其他與分期預付300美元訂單結束
作為OrderAmt_Category結束
從MyOrder;
清單7:巢狀語句
清單7中的程式碼類似於清單6中的程式碼。 唯一的區別是我新增了一個額外的情況下表達是否有秩序MyOrder表購買使用禮物選項,只允許購買超過200美元。 記住當你窩案表示式SQL伺服器只允許有10的巢狀級別。
可以使用其他地方的表達
到目前為止,我所有的示例使用表示式建立一個結果字串通過將選擇列表的情況下表達TSQL select語句。 您還可以使用一個案例表達在一個更新,刪除和設定語句。 另外這樣表達可以結合使用,在那裡,ORDER BY和條款。 在清單8中,我使用一個案例表達一個WHERE子句。
SELECT *
從MyOrder
情況下一年(OrderDT)
當2014年“第一年”
當2013年之後的第二年
當2012年“三年級”
其他4和超越的結束=“1年”;
清單8:在WHERE子句中使用情況下表達
在清單8中我只想返回的訂單MyOrder表中的行“第一年”。 為此我把相同的情況下表達我在WHERE子句中使用清單2中。 我用條件表示式的左邊部分,所以它會產生不同的基於“…年”字串OrderDT列。 然後我測試產生這樣的字串表示式是否等於“1年”的價值,一行的時候將會回來MyOrder表。 記住我不建議使用案例表達選擇日期的日期列使用“1年”這樣的刺痛,當有其他更好的方法,如使用一年對於一個給定的函式選擇行。 我只有這樣做來演示如何使用一個CASE語句的WHERE子句。
簡化表示式使用IIF函式
與SQL Server 2012的引入,微軟新增IIF函式。 IIF函式可以被認為是一個快捷方式到CASE語句。 在圖2中您可以找到IIF的語法功能。
國際金融協會(boolean_expression true_value false_value)
圖2:IIF的語法功能
“Boolean_expression”是一個有效的布林表示式,相當於真或假。 當布林表示式等同於真實價值然後執行“true_value”表示式。 如果布林表示式相當於假“false_value”執行。 就像這樣表達IIF函式可以巢狀10水平。
使用IIF的例子
演示如何使用IIF函式替換表示式中,讓我們來回顧一下的程式碼使用一個搜尋表示式在清單9中。
選擇OrderAmt,
情況下
當OrderAmt > 200美元高階的
其他低美元訂單的最終訂單型別
從MyOrder;
清單9:簡單表示式的例子
清單9中的程式碼時只有一個表示式,用於確定OrderAmt要麼是高或低美元的訂單。 如果當表達“OrderAMT > 200”真的,那麼評估訂單型別值設定為“高美元的秩序”。 如果當表示式的求值結果為FALSE,那麼“低美元秩序”設定的訂單型別價值。
重寫程式碼使用一個IIF函式而不是表達在清單10中可以找到。
選擇OrderAmt,
國際金融協會(OrderAmt > 200年,
“高美元訂單”,
“低美元訂單”)作為訂單型別
從MyOrder;
清單10:使用IIF函式示例
清單10通過觀察可以看到為什麼IIF函式被認為是速記版本的情況下表達。 情況下被替換為”一詞IIF(”字串,“然後”條款被替換為一個逗號,“其他”條款被替換為一個逗號和“結束”被替換為“)”。 當布林表示式”OrderAmt > 200“真值”顯示高美元的秩序”。 當布林表示式的OrderAmt > 200“假然後評估”顯示低美元的秩序”。 如果你執行清單9和10中的程式碼你會看到他們都產生相同的輸出。
IIF巢狀函式的例子
就像這樣表達SQL Server允許您巢IIF功能。 在清單11中是IIF巢狀函式的一個例子。
選擇OrderAmt,
國際金融協會(OrderAmt < 100
< 100美元的訂單,
(IIF(OrderAmt < 200,
100美元的訂單,
(IIF(OrderAmt < 300,
(IIF(分期預付= ' N ',
200美元的訂單沒有禮物,
200美元訂單預約購物
)
),
(IIF(分期預付= ' N ',
300美元的訂單沒有禮物,
300美元訂單預約購物
)
)
)
)
)
)
)作為OrderAmt_Category
從MyOrder;
清單11:巢狀一個IIF函式的例子
在這個例子中你可以看到,我已經多次IIF函式使用。 每增加一個是用於“真實價值”或“假價值”的國際金融功能。 清單11中的程式碼相當於使用巢狀的情況下表示式的程式碼如清單7所示。
限制
如同大多數TSQL功能有限制。 下面是一些限制要注意關於此案,IIF構造。
案例表達的侷限性:
- 你可以只有10水平的巢狀表示式。
- 情況下表示式不能用於控制流TSQL語句的執行。
IIF功能限制:
- 你只能有10水平的巢狀IIF條款。
總結
表達和IIF功能允許您將表達邏輯TSQL程式碼中,將改變您的程式碼的結果基於一個表示式的計算結果。 通過比較表達支援的國際金融功能和表達你可以有不同的程式碼塊執行取決於比較表示式的求值結果為TRUE或FALSE。 表達和IIF函式提供你程式設計控制以滿足業務需求,否則你可能不會有。
問題和答案
在本節中,您可以檢查你懂了如何使用案例和IIF構造通過回答下列問題。
問題1:
有兩種不同的語法差異的案例表達:簡單搜尋。 這兩個語句下面最好的描述一個簡單的區別和搜尋表示式(選擇兩個)。
- 簡單的例子中語法只支援相等操作符,而搜尋語法支援多個運營商
- 簡單的語法支援多個運營商,而搜尋語法只支援相等操作符
- 簡單的例子中語法WHEN子句有其指定的布林表示式後,而搜尋語法有布林表示式的左邊CASE語句後,和布林表示式的右邊WHEN子句之後。
- 簡單的語法有左邊的布林表示式的CASE語句和右側後WHEN子句後的布林表示式,而搜尋情況下表達WHEN子句後的布林表示式
問題2:
如果表達有多個條款時,評估為TRUE,然後執行/ ELSE子句嗎?
- 然後表示式的最後當條款執行評估為TRUE。
- 然後表示式的第一個值為TRUE的條款執行。
- 所有表示式的值為TRUE的條款執行。
- 執行其他表示式
問題3:
多少個巢狀級別情況下表達或IIF功能有嗎?
- 8
- 10
- 16
- 32
答案:
問題1:
答案是a和d。一個簡單的CASE語句只能使用相等操作符,而搜尋情況下表達可以處理多個運營商和以及複雜的布林表示式。 另外簡單的語法有左手相等操作符的一部分後,右手一詞的一部分相等操作符後當這個詞。 搜尋案例表達完成布林運算(左手部分運營商的右手部分)後WHEN子句
問題2:
正確的答案是b。如果多個條款評估為TRUE時然後SQL Server只有執行的第一條款部分評估為TRUE。 所有其他條款對任何其他條款,評估為TRUE時跳過。
問題3:
正確的答案是b。這樣表達和IIF函式只支援10巢狀的水平。