SQL Case语句错误地替换了值

Question

我正在尝试重命名我拥有只读访问权限的数据库的一列的值。我还想将其中的两个值合并为一个。我正在使用SQL CASE查询，但它不能正确地转换值。下面是我的SQL：

SELECT
DATE(appraisal.created_time),
appraisal_steps.title,
Count(appraisal.appraisal_id),
case appraisal_steps.title
 when "archive" then "A_Archived"
 when "on_sale" then "J_Selling"
 when "under_evaluation" then "F_Evaluating"
 when "evaluated" then "H_Appraisal Complete"
 when "pending" then "B_Pending"
 when "crap" then "C_Not For Lofty"
 when "waiting_internal_expert_evaluation" then "D_Unclaimed"
 when ("expert_needs_information" OR "admin_needs_information") then "E_Waiting"
 when "seller_answered_to_expert_question" then "G_Needs_Attn"
 when "ready_to_be_sold" then "I_Ready"
 else "wtf"
end as Status

FROM
appraisal
INNER JOIN appraisal_steps ON appraisal.`status` = appraisal_steps.step_id
WHERE
appraisal.powersale = 0
GROUP BY
DATE(appraisal.created_time),
appraisal_steps.title
ORDER BY
appraisal_steps.title DESC, status

对于某些状态(是复数stati？:)，结果是正确的，但seller_answered_to_expert_question被转换为"E_Waiting"，这是不正确的。

如果我改变"When“子句的顺序，不同的状态会起作用和不起作用。

我做错了什么？

Answer 1

问题是，格式为CASE field WHEN criteria THEN END的CASE语句不允许通过OR或AND使用多个条件，因此WHEN行中的第二个条件不会与标题字段中的值进行比较，在本例中，"G_Needs_Attn“、"I_Ready”或"wtf“中没有任何内容。

你可以通过几种方式来解决这个问题：

将你的OR一分为二：

 when "expert_needs_information" then "E_Waiting"
 when "admin_needs_information" then "E_Waiting"

或者使用以下格式的CASE语句：

    SELECT
    DATE(appraisal.created_time),
    appraisal_steps.title,
    Count(appraisal.appraisal_id),
    case when appraisal_steps.title = "archive" then "A_Archived"
     when appraisal_steps.title = "on_sale" then "J_Selling"
     when appraisal_steps.title = "under_evaluation" then "F_Evaluating"
     when appraisal_steps.title = "evaluated" then "H_Appraisal Complete"
     when appraisal_steps.title = "pending" then "B_Pending"
     when appraisal_steps.title = "crap" then "C_Not For Lofty"
     when appraisal_steps.title = "waiting_internal_expert_evaluation" then "D_Unclaimed"
     when appraisal_steps.title = "expert_needs_information" OR appraisal_steps.title = "admin_needs_information" then "E_Waiting"
     when appraisal_steps.title = "seller_answered_to_expert_question" then "G_Needs_Attn"
     when appraisal_steps.title = "ready_to_be_sold" then "I_Ready"
     else "wtf"
    end as Status       
    FROM
    appraisal
    INNER JOIN appraisal_steps ON appraisal.`status` = appraisal_steps.step_id
    WHERE
    appraisal.powersale = 0
    GROUP BY
    DATE(appraisal.created_time),
    appraisal_steps.title
    ORDER BY
    appraisal_steps.title DESC, status

此格式允许评估多个字段的标准，或同一字段的多个标准。您也可以对该行使用when appraisal_steps.title IN ("expert_needs_information","admin_needs_information") then "E_Waiting"。

这里有一个演示，展示了错误的OR如何最终成为一个包罗万象的东西：SQL Fiddle