如何理解CASE表達式作用及使用SQLServerCASE 表達式代替動態SQL

本篇文章為大家展示了如何理解CASE表達式作用及使用SQLServerCASE 表達式代替動態SQL，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

　　SQLServerCASE表達式可以代替動態SQL語句，使進行大量更新查詢操作時可以提高查詢的效率。下面億速云小編來講解下CASE表達式作用有哪些?如何使用SQLServerCASE表達式代替動態SQL?

　　CASE表達式作用有哪些

　　CASE語句是一個非常強大而有用的工具，你可以用它來解決你的SQLServer查詢問題。你可能已經可以很熟練地在執行SELECT命令的時候使用它來模擬IF/ELSE從句處理。不過，它的功用可遠遠不限于這類型的處理。

　　CASE表達式可以用來：

　　用于更新行的時候，避免使用光標回路

　　在使用合計函數的時候執行專門的處理

　　創建動態ORDERBY和WHERE從句而無需使用動態SQL

　　現在讓我們來看看一些應用例子：

　　首先，新建一個名為Customer的表，插入一些行：

　　CREATETABLEdbo.Customer

　　(

　　customeridINTIDENTITYPRIMARYKEY,

　　firstnameVARCHAR(40)NOTNULL,

　　lastnameVARCHAR(40)NOTNULL,

　　statecodeVARCHAR(2)NOTNULL,

　　totalsalesmoneyNOTNULLDEFAULT0.00

　　)

　　INSERTINTOdbo.Customer(firstname,lastname,statecode,totalsales)

　　SELECT'Thomas','Jefferson','VA',100.00

　　INSERTINTOdbo.Customer(firstname,lastname,statecode,totalsales)

　　SELECT'John','Adams','MA',200.00

　　INSERTINTOdbo.Customer(firstname,lastname,statecode,totalsales)

　　SELECT'Paul','Revere','MA',300.00

　　INSERTINTOdbo.Customer(firstname,lastname,statecode,totalsales)

　　SELECT'Ben','Franklin','PA',400.00

　　GO

　　如何使用SQLServerCASE表達式代替動態SQL

　　例一

　　現在遇到一個需要向表添加一個狀態描述列用于生成所需報表的要求。你當然可以使用指針掃描整個表，達到更新每一行的目的，但是這樣做很花時間，降低系統性能表現。你也可以創建多個UPDATE語句，但這樣也好不到哪里去。但是，你可以把一個UPDATE語句和CASE結合使用，這樣可以只需要一個SET操作就能夠高效率地更新整個表。

　　ALTERTABLEdbo.CustomerADDstatedescriptionVARCHAR(50)NULL

　　GO

　　UPDATEdbo.Customer

　　SETstateDescription=CASEWHENstatecode='MA'THEN'Massachusetts'

　　WHENstatecode='VA'THEN'Virginia'

　　WHENstatecode='PA'THEN'Pennsylvania'

　　ELSENULL

　　END

　　例二

　　現在我們又接到第二個請求，需要報告所有客戶的總數、Massachusetts客戶的總數以及所有Massachusetts客戶的平均銷量。我們當然可以把查詢范圍限制為Massachusetts客戶，但是這樣要獲得所有客戶總數就很麻煩。要解決這個問題，你可以編寫一個在合計函數里使用CASE表達的查詢，就能夠獲得Massachusetts客戶的信息了：

　　SELECTCOUNT(*)ASTotalCustomers,

　　SUM(CASEWHENstatecode='MA'THEN1ELSENULLEND)ASTotalMassCustomers,

　　AVG(CASEWHENstatecode='MA'THENtotalsalesELSENULLEND)ASTotalMassSales

　　FROMdbo.Customer

　　因為執行合計函數的時候會忽略NULL值，我們可以輕松獲得需要的總數。

　　現在又來了新的請求。我們需要一個可以被應用程序調用的存儲過程，但是用戶希望能夠可以通過firstname或lastname來排序。你可能會想要用動態SQL來解決這個問題，不過我們還可以使用CASE來代替動態SQL:

　　CREATEPROCEDUREdbo.getCustomerData@sortbyVARCHAR(9),@sortdirectionCHAR(4)

　　AS

　　SETnocountON

　　SELECTcustomerid,firstname,lastname,statecode,statedescription,totalsales

　　FROMdbo.Customer

　　ORDERBY

　　CASE@sortdirection

　　WHEN'asc'THEN

　　CASE@sortby

　　WHEN'firstname'THENfirstname

　　WHEN'lastname'THENlastname

　　END

　　END

　　ASC,

　　CASE@sortdirection

　　WHEN'desc'THEN

　　CASE@sortby

　　WHEN'firstname'THENfirstname

　　WHEN'lastname'THENlastname

　　END

　　END

　　DESC

　　GO

　　EXECdbo.getCustomerData'lastname','desc'

　　例四

　　最后一個請求。我們需要修改上述的存儲過程來達到通過某個特定狀態來查找客戶。假如客戶狀態被省略，則返回所有狀態的客戶。

　　ALTERPROCEDUREdbo.getCustomerData@sortbyVARCHAR(9),@sortdirectionCHAR(4),@statecodeVARCHAR(2)=NULL

　　AS

　　SETnocountON

　　SELECTcustomerid,firstname,lastname,statecode,statedescription,totalsales

　　FROMdbo.Customer

　　WHEREstatecode=CASEWHEN@statecodeISNOTNULLTHEN@statecode

　　ELSEstatecode

　　END

　　ORDERBY

　　CASE@sortdirection

　　WHEN'asc'THEN

　　CASE@sortby

　　WHEN'firstname'THENfirstname

　　WHEN'lastname'THENlastname

　　END

　　END

　　ASC,

　　CASE@sortdirection

　　WHEN'desc'THEN

　　CASE@sortby

　　WHEN'firstname'THENfirstname

　　WHEN'lastname'THENlastname

　　END

　　END

　　DESC

　　GO

　　EXECdbo.getCustomerData'lastname','desc','MA'

上述內容就是如何理解CASE表達式作用及使用SQLServerCASE 表達式代替動態SQL，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。