MySQL 在 WHERE 子句中使用 CASE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14614573/
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
using CASE in the WHERE clause
提问by phz
simplified version of my query
我的查询的简化版本
SELECT *
FROM logs
WHERE pw='correct' AND CASE WHEN id<800 THEN success=1 ELSE END
AND YEAR(timestamp)=2011
this doesn't work. What i'm trying to do is to add in success=1only for rows with id<800, else ignore this check.
这不起作用。我要做的是success=1仅添加带有 的行id<800，否则忽略此检查。
how do i write this? thanks!
我怎么写这个？谢谢！
edit: to clarify, this what the table looks like
编辑：澄清一下，这是表格的样子
|id | pw | success |
--------------------------
|700 | correct | 1 |
|710 | correct | 1 |
|900 | correct | NULL |
|999 | correct | 0 |
I'm trying to return all the rows, the column pwcannot be ignored.
我正在尝试返回所有行，pw不能忽略该列。
回答by fthiella
You don't have to use CASE...WHEN, you could use an OR condition, like this:
您不必使用 CASE...WHEN，您可以使用 OR 条件，如下所示：
WHERE
pw='correct'
AND (id>=800 OR success=1)
AND YEAR(timestamp)=2011
this means that if id<800, success has to be 1 for the condition to be evaluated as true. Otherwise, it will be true anyway.
这意味着如果 id<800，成功必须为 1，条件才能被评估为真。否则，无论如何它都会是真的。
It is less common, however you could still use CASE WHEN, like this:
它不太常见，但是您仍然可以使用 CASE WHEN，如下所示：
WHERE
pw='correct'
AND CASE WHEN id<800 THEN success=1 ELSE TRUE END
AND YEAR(timestamp)=2011
this means: return success=1(which can be TRUE or FALSE) in case id<800, or always return TRUE otherwise.
这意味着：success=1在 id<800 的情况下返回（可以是 TRUE 或 FALSE），否则总是返回 TRUE。
回答by Chirag
SELECT *
FROM logs
WHERE pw='correct'
AND CASE
WHEN id<800 THEN success=1
ELSE 1=1
END
AND YEAR(TIMESTAMP)=2011
回答by Art
This is working Oracle example but it should work in MySQL too.
这是工作 Oracle 示例，但它也应该在 MySQL 中工作。
You are missing smth - see IN after END Replace 'IN' with '=' sign for a single value.
您缺少 smth - 在 END 后查看 IN 将 'IN' 替换为 '=' 符号以获取单个值。
SELECT empno, ename, job
FROM scott.emp
WHERE (CASE WHEN job = 'MANAGER' THEN '1'
WHEN job = 'CLERK' THEN '2'
ELSE '0' END) IN (1, 2)
回答by Rok Kralj
You can transform logical implication A => Bto NOT A or B. This is one of the most basic laws of logic. In your case it is something like this:
您可以将逻辑含义转换A => B为NOT A or B. 这是最基本的逻辑法则之一。在你的情况下是这样的：
SELECT *
FROM logs
WHERE pw='correct' AND (id>=800 OR success=1)
AND YEAR(timestamp)=2011
I also transformed NOT id<800to id>=800, which is also pretty basic.
我也转换NOT id<800为id>=800，这也是非常基本的。
