这样的业务要求很常见,但要写好得花点心思。要求“一年以上”很简单,在这不做叙述。我们看看“本月及下月”这个需求在MySQL里有没有优雅一点的解决办法。
我的环境为MySQL 8.0.27+Win7 64bit+SQLyog
1. 这还不简单?本月就是now()
查入职时间的月份>=now()月,并且<=now()的下一个月呗
先来创建DB表添加数据
来,我们试试吧
SELECT NOW(),e.* FROM employee_indate e
WHERE MONTH(e_indate) >=MONTH(NOW())
AND MONTH(e_indate) <= MONTH(DATE_ADD(NOW(),INTERVAL 1 MONTH))
ORDER BY e_indate DESC
运行结果:
看起来不错哦
2. 但你忘了如果now()是12月份的话,会怎么样?
我将系统时间调至2021年12月4日,运行相同SQL语句
傻眼了吧明明有12月份入职的员工:
想明白了吗?
原因就是:12不可能小于等于01,所以不会有数据被查出
3. 同事给我出主意,那你直接or一下:入职日期=本月or下月?
这种“办法”其实都不叫办法,约等于固定代码。万一后面需求变更要看三个月的数据,总不能连着三个or吧,不雅观!但是单纯为了解决问题,我们不妨也试试:
SELECT NOW(),e.* FROM employee_indate e
WHERE (
MONTH(e_indate) = MONTH(NOW())
OR
MONTH(e_indate) = MONTH(DATE_ADD(NOW(),INTERVAL 1 MONTH))
)
ORDER BY e_indate DESC
运行结果:
虽然能出来结果,但我是不甘心用这种代码的。
4. 快说最终办法吧
既然是每12个月算一个周期,那么有周期我们就可以做Mod取余简化问题。
我们将入职日期与当前日期做 月差。
例如:2021-01-04距离今天(2022年1月5日)就是12个月零一天,我们只对月的部分取12的余数为0;2016-09-26距离今天63个月10天,取月做12取余为3。
我们可以找到,余数为0的就是当月入职:
于是可以很快的写出SQL如下:
SELECT e.*,NOW(),
TIMESTAMPDIFF(MONTH,e_indate,NOW()) cha,
TIMESTAMPDIFF(MONTH,e_indate,NOW())%12 mod_cha
FROM employee_indate e
运行结果:
可是,不对啊(图中选择部分)怎么上月入职(12月)的记录也出现了?
不知道你注意到没有,上面“月差”计算举例的时候,我对零几天做了标红处理。
2018-12-15距离2022-1-5今天,是36个月21天,不足37个月自然取余就等于0了。
我们不想要这种结果,很简单:都统一取到每个月的月底进行比较,这样就真正实现了只算月差。
SELECT e.*,NOW(),
TIMESTAMPDIFF(MONTH,LAST_DAY(e_indate),LAST_DAY(NOW()))cha,
TIMESTAMPDIFF(MONTH,LAST_DAY(e_indate),LAST_DAY(NOW()))%12mod_cha
FROM employee_indate e
运行结果:
这样2018-12-15换成2018-12月底距今(2022-1月底)就是37个月,取余为1;2016-09-26换成2016-9月底距今(2022-1月底)为64个月,余4;2021-01-04换成2021-1月底距今(2022-1月底)为12个月,余0。
我们就能找到如下规律:
余数为0的,入职时间在本月;
余数为1的,入职时间在上个月;
……
余数为11的,入职时间在次月。
已经看到胜利的曙光了:找到本月及次月的。
但是一个是0,一个是11,怎么弄?还得用or?
5. 最终办法
取余是取周期
0% 12 = 0
11% 12 = 11
那么我+1,得到什么结果呢
(0+1) % 12 = 1
(11+1) % 12 = 0
那么,我只要<=1就可以找到本月及次月的数据了,问题解决!
SQL如下:
SELECT e.*,NOW(),
TIMESTAMPDIFF(MONTH,LAST_DAY(e_indate),LAST_DAY(NOW()))cha,
(TIMESTAMPDIFF(MONTH,LAST_DAY(e_indate),LAST_DAY(NOW()))+1)%12 mod_cha_jia_yi
FROM employee_indate e
运行结果:
★★★★★★★★★★★★★★★★★★★
★★★ 正式放到where中筛选 ★★★
★★★★★★★★★★★★★★★★★★★
SELECT e.*
FROM employee_indate e
WHERE (TIMESTAMPDIFF(MONTH,LAST_DAY(e_indate),LAST_DAY(NOW()))+1)%12 <= 1
ORDER BY e_indate;
运行结果:
~完美~
对了,要想求接下来3个月的入职员工怎么办?
将3作为参数,替换刚才的1即可,效果如图: