时间戳

首页 > 解决方案 > MySQL Case 语句“调整”

mysql - MySQL Case 语句“调整”

问题描述

我有以下工作查询，以小时为单位计算员工应计时间，但我需要将 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”来覆盖以下情况：

标签： mysql

解决方案

您可以使用“子案例”：

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

我更喜欢“子案例”，因为它更能准确地捕捉到你用英语表达的意图，但选择你觉得更容易阅读的那个。

推荐阅读