使用CASE语句的多个条件 [英] Multiple conditions with CASE statements
本文介绍了使用CASE语句的多个条件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要查询一些数据.这是我构造的查询,但对我来说不起作用.对于此示例,我正在使用AdventureWorks数据库.
I need to query some data. here is the query that i have constructed but which isn't workig fine for me. For this example I am using AdventureWorks database.
SELECT * FROM [Purchasing].[Vendor] WHERE PurchasingWebServiceURL LIKE
case
// In this case I need all rows to be returned if @url is '' or 'ALL' or NULL
when (@url IS null OR @url = '' OR @url = 'ALL') then ('''%'' AND PurchasingWebServiceURL IS NULL')
//I need all records which are blank here including nulls
when (@url = 'blank') then (''''' AND PurchasingWebServiceURL IS NULL' )
//n this condition I need all record which are not like a particular value
when (@url = 'fail') then ('''%'' AND PurchasingWebServiceURL NOT LIKE ''%treyresearch%''' )
//Else Match the records which are `LIKE` the input value
else '%' + @url + '%'
end
这对我不起作用.在同一个 CASE
的 THEN
中,如何有多个where条件子句?我该如何进行这项工作?
This is not working for me. How can I have multiple where condition clauses in the THEN
of the the same CASE
? How can I make this work?
推荐答案
基于amadan的另一种方法:
Another way based on amadan:
SELECT * FROM [Purchasing].[Vendor] WHERE
( (@url IS null OR @url = '' OR @url = 'ALL') and PurchasingWebServiceURL LIKE '%')
or
( @url = 'blank' and PurchasingWebServiceURL = '')
or
(@url = 'fail' and PurchasingWebServiceURL NOT LIKE '%treyresearch%')
or( (@url not in ('fail','blank','','ALL') and @url is not null and
PurchasingWebServiceUrl Like '%'+@ur+'%')
END
这篇关于使用CASE语句的多个条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文