具有2个条件的MySQL案例

I have a simple case like here, anyone can help? I have two table here COMPLAINT and HANDLING now i want to select from those table with 2 condition where in HANDLING table is null, and date from COMPLAINT is more than 5 days from sysdate. If I use SQL code like this, it will be error :

我有一个像这里的简单案例，任何人都可以帮忙吗？我在这里有两个表COMPLAINT和HANDLING现在我想从那些具有2个条件的表中选择HANDLING表中的空值，而来自COMPLAINT的日期是从sysdate超过5天。如果我使用这样的SQL代码，那将是错误的：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE WHEN B.CID IS NULL AND (SELECT SYSDATE FROM DUAL)- A.SUBMIT_DATE > 5 TH' at line 2

您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册，以便在'CASE WHEN B.CID为空并且（从SELECT选择SYSDATE）附近使用正确的语法 - A.SUBMIT_DATE> 5 TH'在第2行

SELECT * FROM complaint a LEFT JOIN handling b ON a.cid=b.cid
CASE 
WHEN B.CID IS NULL AND (SELECT SYSDATE FROM DUAL)- A.`SUBMIT_DATE` > 5 
THEN 'OVER'
ELSE 'CLEAR'
END

2 个解决方案

#1

The case statement you have added is in the wrong place and you can not use it in the joining clause as you are doing.

您添加的case语句位于错误的位置，并且您无法在join子句中使用它。

However if you want select something using case when then it must be in the select clause something as

但是如果你想用case选择一些东西那么它必须在select子句中

select
*, 
case 
 when b.cid is null and datediff(curdate(),a.SUBMIT_DATE) > 5 then 'OVER'
 else 'CLEAR'
end as `some_col_name`
from complaint a LEFT JOIN handling b ON a.cid=b.cid

#2

Case statement must be in select and not after join as below :

Case语句必须在select中，而不是在join之后，如下所示：

SELECT *,
CASE 
WHEN B.CID IS NULL AND datediff(curdate(), a.SUBMIT_DATE) > 5 
THEN 'OVER'
ELSE 'CLEAR'
END AS 'YOUR_COL'
FROM complaint a LEFT JOIN handling b ON a.cid=b.cid

#1