MySQL SQL WHERE 子句中的 CASE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12400574/
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
CASE Statement in SQL WHERE clause
提问by guri
I'm trying to fetch data from table where I'm using a CASEcondition in the WHEREclause and currently I'm using following query:-
我正在尝试从我CASE在WHERE子句中使用条件的表中获取数据，目前我正在使用以下查询：-
SELECT count(enq_id) AS total, sum(purchase_amount) AS purchase
FROM temp_stock
WHERE purchase_date <> '0000-00-00'
AND purchase_date < '2012-08-01'
AND (
STATUS = 'Sold'
OR STATUS = 'In Stock'
OR STATUS = 'Ref'
)
AND CASE WHEN (
STATUS = 'Sold'
)
THEN delivery_date >= '2012-08-01'
END
But it returns 0for totaland NULLfor purchase.
但它返回0fortotal和NULLfor purchase。
回答by hims056
From your comment.
从你的评论。
I want to use Case Statement, could u pls clarify me about case statament in where clause
我想使用Case Statement，你能否澄清一下where子句中的case statament
You can use CASEstatement in WHERElike this:
您可以像这样使用CASE语句WHERE：
SELECT count(enq_id) AS total, sum(purchase_amount) AS purchase
FROM temp_stock
WHERE purchase_date <> '0000-00-00'
AND purchase_date < '2012-08-01'
AND ( STATUS = 'Sold'
OR STATUS = 'In Stock'
OR STATUS = 'Ref')
AND CASE STATUS
WHEN 'Sold'
THEN delivery_date >= '2012-08-01'
ELSE 1=1
END
Here you need to use ELSE 1=1. otherwise you will not get desired result. For more explanation see this SQLFiddle
在这里你需要使用ELSE 1=1. 否则你不会得到想要的结果。有关更多解释，请参阅此 SQLFiddle
回答by Ian Clelland
I don't think that CASE can work that way. What you want is a slightly more complex expression as your WHERE clause. Probably something like this:
我不认为 CASE 可以那样工作。你想要的是一个稍微复杂一点的表达式作为你的 WHERE 子句。大概是这样的：
SELECT count(enq_id) AS total, sum(purchase_amount) AS purchase
FROM temp_stock
WHERE purchase_date <> '0000-00-00'
AND purchase_date < '2012-08-01'
AND (
(STATUS = 'Sold' AND delivery_date >= '2012-08-01')
OR STATUS = 'In Stock'
OR STATUS = 'Ref'
)
