MySQL Case 语句 "tweak"

Question

我有以下工作查询，以小时为单位计算员工应计时间，但我需要将 11 & 14 更改为 (14 & 18) IF manager=1

这是我当前的代码:

CASE 
WHEN TIMESTAMPDIFF(DAY,hiredate,NOW()) < 1
THEN '0'
WHEN TIMESTAMPDIFF(DAY,hiredate,NOW()) >= 1 AND TIMESTAMPDIFF(MONTH, 
hiredate, NOW()) <= 60
THEN '11'
WHEN TIMESTAMPDIFF(MONTH, hiredate, NOW()) >= 61 AND TIMESTAMPDIFF(MONTH, 
hiredate, NOW()) <= 120
THEN '14'
ELSE '18'    
END AS monthly_Accrual_Level

如何添加额外变量“manager=1”来覆盖以下情况:

• 当结果 = 11，但员工是经理时，让他的时间 = 14
• 当结果 = 14，但员工是经理时，让他的时间 = 18

Answer 1

您可以使用“子案例”:

CASE
...
WHEN TIMESTAMPDIFF(DAY,hiredate,NOW()) >= 1 AND TIMESTAMPDIFF(MONTH, 
hiredate, NOW()) <= 60
THEN CASE WHEN manager = 1 THEN '14' ELSE '11' END
... —- similar for other manager value
END AS monthly_Accrual_Level

或将条件的每一侧添加到 WHEN:

CASE
...
WHEN TIMESTAMPDIFF(DAY,hiredate,NOW()) >= 1 AND TIMESTAMPDIFF(MONTH, 
hiredate, NOW()) <= 60
AND manager = 1 THEN '14'
WHEN TIMESTAMPDIFF(DAY,hiredate,NOW()) >= 1 AND TIMESTAMPDIFF(MONTH, 
hiredate, NOW()) <= 60
AND manager != 1 THEN '11'
... —- similar for other manager value
END AS monthly_Accrual_Level

我更喜欢“子案例”，因为它更贴切地表达了您用英语表达的意图，但请选择您觉得更容易阅读的那个。