最近一周生日提醒

使用MySQL数据库查询最近一周生日

SELECT  DATE_FORMAT(birthday,'%c-%e' ) AS newbirthday

FROM `t_contact`
WHERE

  DATEDIFF( DATE_FORMAT(birthday, concat(year(now()),'-%m-%d')), date(now()) ) < 7
and

  DATEDIFF( DATE_FORMAT(birthday, concat(year(now()),'-%m-%d')),date(now())) >= 0
ORDER BY newbirthday ASC

 

这个查询解决了跨月的问题，但没有解决跨年的问题，要想解决跨年的问题

 

SELECT  DATE_FORMAT(birthday,'%c-%e' ) AS newbirthday

FROM `t_contact`
WHERE

(DATEDIFF( DATE_FORMAT(birthday, concat(year(now()),'-%m-%d')), date(now()) ) < 7
and DATEDIFF( DATE_FORMAT(birthday, concat(year(now()),'-%m-%d')),date(now())) >= 0)

or

(DATEDIFF( DATE_FORMAT(birthday, concat(year(now())+1,'-%m-%d')), date(now()) ) < 7
and DATEDIFF( DATE_FORMAT(birthday, concat(year(now())+1,'-%m-%d')),date(now())) >= 0)
ORDER BY newbirthday ASC

 

这样就能查询出我们需要的数据了，但如果想按最先到达的生日提醒排序(考虑到跨月和跨年的问题)，就需要将排序设置为

ORDER BY datediff(now(),birthday) DESC