如何理解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，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。