我从一个使用嵌套 If 语句的同事那里继承了一个电子表格,如下所示:
=IF(AND([@[Pred Type]]="PR_FS",[@[ST Finish]]="A"),
0,
IF(AND([@[Pred Type]]="PR_SS", OR([@[ACT_START_DATE]]<>0,[@[ST Start]]="A")),
0,
IF(AND([@[Pred Type]]="PR_SF",OR([@[ACT_START_DATE]]<>0,[@[ST Start]]="A")),
0,
IF(AND([@[Pred Type]]="PR_FF",[@[STFinish]]="A"),0,1)
)
)
)
我知道我可以写一个 CASE 语句来模仿这个,但无法弄清楚它的语法。我试过了:
,CASE WHEN #Preds.Pred_Type = 'PR_FS' and #Tbl_Sched_Act.Expected_Finish = 'A' THEN 0
WHEN #Preds.Pred_Type = 'PR_SS' OR ACT_START_DATE IS NOT NULL OR Start_RAW IS NOT NULL THEN 0
WHEN #Preds.Pred_Type = 'PR_SF'OR ACT_START_DATE IS NOT NULL OR Start_RAW IS NOT NULL THEN 0
WHEN #Preds.Pred_Type = 'PR_FF' and #Tbl_Sched_Act.Expected_Finish = 'A' THEN 0
ELSE 1
END as PRED_MET
但是,CASE 语句不会计算任何等于 1 的项目。它们的计算结果都为 0,这是不正确的。
有没有人知道我如何正确编写我的 CASE 语句?
您需要将OR
分组在括号内()
以强制运算符优先级。
CASE
WHEN #Preds.Pred_Type = 'PR_FS' and #Tbl_Sched_Act.Expected_Finish = 'A'
THEN 0
WHEN #Preds.Pred_Type = 'PR_SS'
AND (ACT_START_DATE <> 0 OR Start_RAW <> 0)
THEN 0
WHEN #Preds.Pred_Type = 'PR_SF'
AND (ACT_START_DATE <> 0 OR Start_RAW <> 0)
THEN 0
WHEN #Preds.Pred_Type = 'PR_FF' and #Tbl_Sched_Act.Expected_Finish = 'A'
THEN 0
ELSE 1
END as PRED_MET
注意::如果一个数字是<> 0
那么也是NOT NULL
演示
但这也可以简化为:
CASE
WHEN #Tbl_Sched_Act.Expected_Finish = 'A'
AND #Preds.Pred_Type IN ('PR_FS', 'PR_FF')
THEN 0
WHEN (ACT_START_DATE <> 0 OR Start_RAW <> 0)
AND (#Preds.Pred_Type IN ('PR_SS','PR_SF')
THEN 0
ELSE 1
END as PRED_MET