mysql CASE WHEN 要么但不是两者

Question

我有一个案例陈述:

case when R1 = risk_factor OR R2 = risk_factor then 1 else 0 end

但它也开始了

case when R1 = risk_factor AND R2 = risk_factor then 1 else 0 end

陈述的正确语法是什么:

case when (R1 = risk_factor or R2 = risk_factor) BUT NOT (R1 = risk_factor AND R2 = risk_factor) then 1 else 0 end   

Answer 1

(R1 = risk_factor or R2 = risk_factor) BUT NOT (R1 = risk_factor AND R2 = risk_factor)

应该是

(R1 = risk_factor or R2 = risk_factor) AND NOT (R1 = risk_factor AND R2 = risk_factor)

但是你也可以使用XOR

(R1 = risk_factor XOR R2 = risk_factor)

Logical XOR. Returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is nonzero, otherwise 0 is returned.

Documentation

但是-在您的特定情况下，您也可以将条件写为

(risk_factor IN (R1, R2) AND R1 <> R2)

注意:如果您的 CASE 表达式类似于 then 1 else 0，那么您根本不需要它。您可以只使用 bool 表达式。但有一个异常(exception):如果您的 bool 表达式可以返回 NULL，而您想将其转换为 0。但在那种情况下，您也可以使用 COALESCE(boolean_expression, 0)。