mysql - 根据加入日期计算员工的下一个 3 周年纪念日

Question

我有一个员工 mysql 数据库表，其中包含员工姓名和开始日期。我希望能够从今天开始计算每位员工即将到来的 3 周年纪念日。 (例如，乔丹的下一个即将到来的周年纪念日是 2019 年 8 月 25 日，李的周年纪念日是 2017 年 9 月 22 日，迈克的周年纪念日是 2018 年 10 月 26 日)

id    name       startDate        
---------------------------
0     Jordan     08/25/2010          
1     Lee        09/22/2014          
2     Mike       10/26/2015        

低效的蛮力解决方案是生成从开始日期到当前日期的所有周年纪念日期加上今天日期之后的一个日期，然后选择它。

但是必须有一个更高效的 SQL 查询来根据今天的日期为每个员工显示下一个即将到来的周年纪念日。必须以某种方式使用 DATEDIFF。

Answer 1

已编辑

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 Fiddle 向您展示它如何处理测试用例: 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() 的注释对比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

不是函数的小写年份 [没有括号] 定义了一个 INTERVAL 类型，您可以使用日、年、月等。

date INTERVAL + 1 year = add 1 year to date

另一个例子是

startDate INTERVAL + NumOfYearsDiff year = add NumOfYearsDiff to startDate

在情况 2 中，比较我所做的是使两个日期本质上都是 YEAR 0001通过从日期中减去年份整数，然后比较哪个日期会告诉我今年晚些时候的日期，以了解周年纪念日是否已经过去或今年仍将到来。 TIMESTAMPDIFF在一定程度上自动为您执行此操作，但如果您使用，则周年纪念日将是下一个，例如2019如果是今天 2016 .所以如果你想像今天一样展示它，你仍然需要检查我所做的相同条件，这意味着没有真正的理由使用 TIMESTAMPDIFF .

这是我的方法、Spencer 的和 Gordon 的并排比较的 sqlfiddle http://www.sqlfiddle.com/#!9/cf276a/3

显示内容:

• 如果周年纪念日是今天，Gordon's & Spencer 的方法会将下一个周年纪念日显示为今天 + 3 年。哪些是需要的，哪些是不需要的？
• Gordon 对 future 日期的回答将显示开始日期而不是开始日期 + 3 年