根据加入日期计算员工的下一个3年周年纪念
我有一个员工mysql数据库表,其中包含员工姓名和开始日期。我希望能够从今天的日期算出每位员工即将到来的3周年纪念。 (因此,例如,乔丹的下一个即将到来的周年纪念将是2019年8月25日,李某的将是2017年9月22日,小李的2018年10月26日)根据加入日期计算员工的下一个3年周年纪念
id name startDate
---------------------------
0 Jordan 08/25/2010
1 Lee 09/22/2014
2 Mike 10/26/2015
低效蛮力解决办法是将生成所有周年从startDate到当前日期的日期加上今天日期之后的一个日期,然后选择它。
但是根据今天的日期,每个员工都有一个更高效的SQL查询来显示下一个即将到来的周年纪念。必须以某种方式使用DATEDIFF。
EDITED
SELECT
*
,CASE
WHEN CURDATE() <= startDate THEN startDate + INTERVAL 3 year
WHEN MOD((YEAR(CURDATE()) - YEAR(startDate)),3) = 0
AND (CURDATE() - INTERVAL YEAR(CURDATE()) year)
<= (startDate - INTERVAL YEAR(startDate) year)
THEN startDate + INTERVAL (YEAR(CURDATE()) - YEAR(startDate)) year
ELSE startDate + INTERVAL (year(CURDATE()) - YEAR(startDate) + 3 - MOD(YEAR(CURDATE()) - YEAR(startDate),3)) year
END as "Next3YrAniversary"
FROM
TableName
SQL小提琴向你展示它与测试用例工作: http://www.sqlfiddle.com/#!9/cf276a/1
反正思路如下:
startDate is Today or in future if so just add 3 years
-
Remainder of (Yesrs Diff/3) = 0
and
if current month/day is <= month/day of startDate
如果是这样的周年是今年,并且还没有发生,那么add number of years between current date and startDate to startDate
。要说这是一种稍微不同的方式,如果它是2016
而周年纪念是2016
它将会看到这个周年是否会在今年发生。 - 否则添加到起始日期:
Number of years between Start Date & Now + 3 years - Remainder of (Years between Now and startDate/3)
的注意事项有关YEAR()
VS year
。你可以写year()
或YEAR()
我倾向于大写我的特定功能等,但year()函数只获取日期年的INT
。因此YEAR(CURDATE())
表示获得当前日期的年份,例如2016
,YEAR(startDate)
获得例如startDate
的年份。 2013
。 MOD()
为您提供了从分裂所以2/3 remainder 1
2016 - 2013 gives you 3 so remainder of 3/3 is 0
下一年的情况下这是不是一个函数[不带括号]余定义的时间间隔类型,你可以使用一天,一年,一个月,等
date INTERVAL + 1 year = add 1 year to date
另一个例子是
startDate INTERVAL + NumOfYearsDiff year = add NumOfYearsDiff to startDate
在第二种情况下进行比较时,我做的是通过减去日期的年份整数使这两个日期基本上是YEAR 0001
,然后比较哪会告诉我哪个日期以便在今年晚些时候了解这个周年纪念日是否已经过去,或者今年还会继续。 TIMESTAMPDIFF
会自动为您取得学位,但如果您使用,那么周年日期将是下一个日期,例如2019
如果是在2016
。所以如果你想今天展示它,你仍然必须检查我所做的相同条件,这意味着没有真正的理由使用TIMESTAMPDIFF
。
这里是我的方法,斯宾塞的侧面比较sqlfiddle侧,和戈登的 http://www.sqlfiddle.com/#!9/cf276a/3
显示内容:
- 如果今天纪念日是戈登的&斯宾塞的方法,会显示下一个周年如今天+ 3年。哪些可能或不可能被期望?
- 戈登的回答为未来的日期将显示起始日期代替的startDate +3年
嗯,我认为这有点麻烦,但你可以在一个声明中做到这一点。
我认为将会发挥作用的逻辑是在几年内取得差异,并向上舍入到最近的3年。然后将其添加到开始日期。如果这大于当前日期,那是下一个开始日期。否则,添加三个岁:
select t.*,
(case when startdate + interval 3 + 3 * floor((year(curdate()) - year(startdate))/3) year > curdate()
then startdate + interval 3 + 3 * floor((year(curdate()) - year(startdate))/3) year
else startdate + interval 6 + 3 * floor((year(curdate()) - year(startdate))/3) year
from t;
这种方法比较接近,但我认为这不适用于startdate'2013-09-30',因为今天的日期是'2016-09- 16' 。从开始日期开始的下一个“三周年纪念日”将在14天,即2016-09-30。 – spencer7593
戈登答案的方法是一个很好的一个。我只是觉得稍作修改是必要的。使用方法:
INTERVAL 0 +
代替INTERVAL 3 +
和到位的INTERVAL 6 +
使用
INTERVAL 3 +
我不想与戈登的回答一塌糊涂。
下面是一个完全相同的方法演示,语法略有不同,并且在将来添加对startdate
的检查,我们只想在startdate
之后添加3年(以获取前三个一周年)。
SELECT t.id
, t.name
, t.startdate
, CASE
WHEN t.startdate > DATE(NOW())
THEN t.startdate + INTERVAL 3 YEAR
WHEN t.startdate + INTERVAL FLOOR(TIMESTAMPDIFF(YEAR,t.startdate,NOW()) /3)*3 YEAR > DATE(NOW())
THEN t.startdate + INTERVAL FLOOR(TIMESTAMPDIFF(YEAR,t.startdate,NOW()) /3)*3 YEAR
ELSE t.startdate + INTERVAL FLOOR(TIMESTAMPDIFF(YEAR,t.startdate,NOW()) /3)*3 + 3 YEAR
END AS next_3yr_anniv
FROM mytable t
ORDER BY ...
@KamilG怎么这样?从今天起的下一个周年将不得不是08/25/2019。 – CodeCrack
你想为闰日聘用的员工做什么? – pilcrow
@CodeCrack闰日实际上是一个很好的问题,但我会假设你并不在乎这么多。我的回答,我相信其他人会假设周年日期将在2/28而不是2/29。这里是一个sqlfiddle,展示了mysql如何将多年的加入视为闰日。 http://www.sqlfiddle.com/#!9/f35e91/5 – Matt