mysql - Case 语句 Mysql 查询根据条件加载数据

Question

我正在尝试使用以下 SQL 查询

SELECT task.id,task.Task,task.TaskCategory,task.IsComplete,
task.AssignedDate,task.CompletionDate, task.AssignedTo,task.templateID,task.ListingID,task.ClosingID,
task.ContactID,task.LeadID,task.statusid, taskcategory.taskCategoryID,taskcategory.Category FROM 
task INNER JOIN taskcategory ON task.TaskCategory = taskcategory.taskCategoryID WHERE 1=1 
and (CompletionDate = CURDATE() OR CompletionDate IS NULL AND task.IsComplete = 0) 
and assignedto = 2345 and id = 2421 and 
CASE(WHEN status_for_design = 0 THEN (leadid is not null or ContactID is not null)
THEN NULL 
END AS status_for_design)
group by 
assignedto ORDER BY task.AssignedDate ASC 

主要目的是如果status_for_design = 0 [使用leadid或contactid不为空，但如果status_for_design = 1

但是我遇到了错误

[Err] 1064 - 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 'WHEN status_for_design = 0 THEN (leadid is not null or ContactID is not null)
T' at line 7

Answer 1

确实，格式化您的查询以便人们可以阅读它会帮助您了解正在发生的事情。

您的 WHERE 子句中有一个 AS 。那是不合适的。我怀疑你想要的查询看起来更像是这样的:

SELECT t.*, tc.taskCategoryID, tc.Category
FROM task t INNER JOIN
     taskcategory tc
     ON t.TaskCategory = tc.taskCategoryID
WHERE 1 = 1 AND
      (CompletionDate = CURDATE() OR CompletionDate IS NULL AND t.IsComplete = 0) AND
      assignedto = 2345 AND
      id = 2421 AND 
      (status_for_design <> 0 or leadid is not null or ContactID is not null)
GROUP BY assignedto
ORDER BY t.AssignedDate ASC ;

请注意，WHERE 中不需要 CASE。我还对 SELECT 中具有未聚合列但不在 GROUP BY 中的查询高度怀疑。