在WHERE子句中使用CASE [英] using CASE in the WHERE clause

问题描述

我的查询的简化版本

SELECT *
FROM logs 
WHERE pw='correct' AND CASE WHEN id<800 THEN success=1 ELSE END 
AND YEAR(timestamp)=2011 

这不起作用.我想做的是仅对具有id<800的行添加success=1，否则忽略此检查.

this doesn't work. What i'm trying to do is to add in success=1 only for rows with id<800, else ignore this check.

我该怎么写?谢谢！

要澄清一下，这是表格的样子

edit: to clarify, this what the table looks like

|id  | pw      | success |
--------------------------
|700 | correct | 1       |
|710 | correct | 1       |
|900 | correct | NULL    |
|999 | correct | 0       |

我正在尝试返回所有行，不能忽略列pw.

I'm trying to return all the rows, the column pw cannot be ignored.

推荐答案

您不必使用CASE ...时，可以使用OR条件，如下所示:

You don't have to use CASE...WHEN, you could use an OR condition, like this:

WHERE
  pw='correct'
  AND (id>=800 OR success=1) 
  AND YEAR(timestamp)=2011

这意味着如果id ＜800，则条件必须为1才能成功.否则，还是如此.

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.

这种情况不太常见，但是您仍然可以使用CASE WHEN，例如:

It is less common, however you could still use CASE WHEN, like this:

WHERE
  pw='correct'
  AND CASE WHEN id<800 THEN success=1 ELSE TRUE END 
  AND YEAR(timestamp)=2011

这意味着:在id为<800的情况下返回success=1(可以为TRUE或FALSE)，否则始终返回TRUE.

this means: return success=1 (which can be TRUE or FALSE) in case id<800, or always return TRUE otherwise.

这篇关于在WHERE子句中使用CASE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！