MySQL Case语句“调整”
MySQL Case语句“调整”
提问于 2018-07-24 23:09:18
我有以下工作查询，它以小时为单位计算员工的应计时间，但我需要将11和14更改为(14 & 18)，如果为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
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-07-24 23:19:32
您可以使用“sub case”：
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我更喜欢“sub case”，因为它更接近你用英语表达的意图，但请选择你觉得更容易阅读的那个。
Stack Overflow用户
发布于 2018-07-25 01:06:31
感谢波西米亚人的大力帮助！
这是我最终得到的解决方案：
CASE
WHEN TIMESTAMPDIFF( DAY, hiredate, NOW( ) ) < 1 THEN '0' WHEN TIMESTAMPDIFF( DAY, hiredate, NOW( ) ) >= 1
AND TIMESTAMPDIFF( MONTH, hiredate, NOW( ) ) <= 60 AND manager != 1 THEN '11' 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( ) ) <= 120 AND manager != 1 THEN '14' WHEN TIMESTAMPDIFF( DAY, hiredate, NOW( ) ) >= 1
AND TIMESTAMPDIFF( MONTH, hiredate, NOW( ) ) <= 120 AND manager = 1 THEN '18'
ELSE '18'
END AS monthly_Accrual_Level页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51502054
复制相关文章
相似问题
腾讯云开发者
