使用WHERE子句中的大小写

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=1 only for rows with id<800, else ignore this check.

这并不工作。我要做的是为id<800的行添加success=1，否则忽略这个检查。

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 pw cannot be ignored.

我试图返回所有的行，pw列不能被忽略。

4 个解决方案

#1

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

你不需要用CASE……当，你可以使用一个或条件，像这样:

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才能将条件评估为true。否则，这将是真的。

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

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

这意味着:如果id<800，返回success=1(可以是TRUE或FALSE)，否则总是返回TRUE。

#2

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

#3

You can transform logical implication A => B to NOT A or B. This is one of the most basic laws of logic. In your case it is something like this:

可以将逻辑蕴涵A => B转换为A或B，这是最基本的逻辑定律之一。你的情况是这样的:

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

I also transformed NOT id<800 to id>=800, which is also pretty basic.

我还将NOT id<800转换为id>=800，这也是非常基本的。

#4

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——请参见后面的“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)

#1