SQL 带有 CASE 语句的多个条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3031501/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple conditions with CASE statements
提问by pavanred
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.
我需要查询一些数据。这是我构建的查询，但对我来说不是很好。在这个例子中，我使用的是 AdventureWorks 数据库。
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
This is not working for me. How can I have multiple where condition clauses in the THENof the the same CASE? How can I make this work?
这对我不起作用。我怎样才能THEN在同一个中拥有多个 where 条件子句CASE？我怎样才能使这项工作？
回答by Amadan
It's not a cut and paste. The CASEexpression must return a value, and you are returning a string containing SQL (which is technically a value but of a wrong type). This is what you wanted to write, I think:
这不是剪切和粘贴。该CASE表达式必须返回一个值，你正在返回包含SQL字符串（这在技术上是一个值，但一个错误的类型）。这就是你想写的，我想：
SELECT * FROM [Purchasing].[Vendor] WHERE
CASE
WHEN @url IS null OR @url = '' OR @url = 'ALL'
THEN PurchasingWebServiceURL LIKE '%'
WHEN @url = 'blank'
THEN PurchasingWebServiceURL = ''
WHEN @url = 'fail'
THEN PurchasingWebServiceURL NOT LIKE '%treyresearch%'
ELSE PurchasingWebServiceURL = '%' + @url + '%'
END
I also suspect that this might not work in some dialects, but can't test now (Oracle, I'm looking at you), due to not having booleans.
我还怀疑这在某些方言中可能不起作用，但由于没有布尔值，现在无法测试（Oracle，我在看着你）。
However, since @urlis not dependent on the table values, why not make three different queries, and choose which to evaluate based on your parameter?
但是，既然@url不依赖于表值，为什么不进行三个不同的查询，并根据您的参数选择要评估的？
回答by kamahl
Another way based on amadan:
另一种基于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
