MySQL查询以根据当前日期排序即将到来的生日 [英] MySQL query to sort upcoming birthdays based on current date

问题描述

我有以下人物和他们的生日：

I have the following table of people and their birthdays:

name        birthday
----------------------
yannis      1979-06-29
natalia     1980-08-19
kostas      1983-10-27    
christos    1979-07-22
kosmas      1978-04-28

我不知道如何对生日到今天的距离进行排序。所以对于NOW（）= 2011-09-08，排序结果应该是：

and I have no idea how to sort the names on how closer the birthday is to today. So for NOW() = 2011-09-08 the sorted result should be:

kostas      1983-10-27
kosmas      1978-04-28
yannis      1979-06-29
christos    1979-07-22
natalia     1980-08-19

我正在寻找一个快速的黑客，不太在乎性能（宠物项目 - 表将持有不到1000条记录），但当然每个建议将非常感谢。

I'm looking for a quick hack, don't really care for performance (pet project - table will hold less than 1000 records), but of course every suggestion will be extremely appreciated.

推荐答案

这是一种方式：

• 计算当前年份 - 出生年份


• 将结果的年数添加到出生日期


• 今年您现在有生日，如果此日期已过，则再增加一年


• 按日期排序结果
• 
  

• Calculate current year - year of birth
• Add the resulting number of years to the date of birth
• You now have the birthday this year, if this date has passed then add one more year
• Sort the results by that date

SELECT
    name,
    birthday,
    birthday + INTERVAL(YEAR(CURRENT_TIMESTAMP) - YEAR(birthday)) + 0 YEAR AS currbirthday,
    birthday + INTERVAL(YEAR(CURRENT_TIMESTAMP) - YEAR(birthday)) + 1 YEAR AS nextbirthday
FROM bd
ORDER BY CASE
    WHEN currbirthday >= CURRENT_TIMESTAMP THEN currbirthday
    ELSE nextbirthday
END

SQLFiddle

这篇关于MySQL查询以根据当前日期排序即将到来的生日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！