MySQL天前基于其他表中的设置

Question

当前查询检查今天是否是客户的生日以及 processTime 是否是这个小时。这就像一个魅力。不过，我们希望调整查询，并在客户生日前 x 天向客户发送电子邮件。这取决于birthday_settings 中的dayOffset 设置。

dayOffset 的范围是 -365 到 +365。

我应该对以下查询进行哪些调整。生日列是日期列

SELECT firstName, lastName, birthday, FROM 
    birthday_customers C
    INNER JOIN
    birthday_settings S
        ON C.customerId = S.customerId
WHERE 
    MONTH(birthday) = MONTH(NOW()) 
    AND DAY(birthday) = DAY(NOW())
    AND processTime = HOUR(NOW())

Answer 1

您只需从当前时间减去天数即可:

SELECT firstName, lastName, birthday
FROM birthday_customers C INNER JOIN
     birthday_settings S
     ON C.customerId = S.customerId
WHERE MONTH(birthday) = MONTH(DATE_SUB(CURDATE(), interval s.DayOffset day) AND
      DAY(birthday) = DAY(DATE_SUB(CURDATE(), interval s.DAYOffset day) AND
      processTime = HOUR(NOW())