mysql 生日排序_MySQL查询以根据当前日期排序即将到来的生日

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

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

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.

解决方案

Here is one way:

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