根据当前日期对即将到来的生日进行排序

发布于 2024-12-03 06:43:22 字数 489 浏览 0 评论 0原文

我有下表的人员及其生日:

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 ,排序结果应该是:

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

我正在寻找一种快速破解方法,并不真正关心性能(宠物项目 - 表将保存少于 1000 条记录),但当然每个我们将非常感谢您的建议。

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

因为看清所以看轻 2024-12-10 06:43:22

一种方法是:

  • 计算当前年份 - 出生年份
  • 将所得的年数添加到出生日期
  • 您现在的生日是年,如果该日期已过,则再添加一年
  • 按该日期对结果进行排序
SELECT
    name,
    birthday,
    birthday + INTERVAL (YEAR(CURRENT_DATE) - YEAR(birthday))     YEAR AS currbirthday,
    birthday + INTERVAL (YEAR(CURRENT_DATE) - YEAR(birthday)) + 1 YEAR AS nextbirthday
FROM birthdays
ORDER BY CASE
    WHEN currbirthday >= CURRENT_DATE THEN currbirthday
    ELSE nextbirthday
END

注意:

  • 无论当前时间如何,今天的生日首先出现 平年的
  • 2 月 29 日生日被视为等于 2 月 28 日生日,例如
    • 2019 年 1 月 1 日,2 月 28 日和 2 月 29 日生日(2019 年)排序相同
    • 2019 年 3 月 1 日,2 月 28 日和 2 月 29 日生日(2020 年)按预期排序

SQLFiddle

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_DATE) - YEAR(birthday))     YEAR AS currbirthday,
    birthday + INTERVAL (YEAR(CURRENT_DATE) - YEAR(birthday)) + 1 YEAR AS nextbirthday
FROM birthdays
ORDER BY CASE
    WHEN currbirthday >= CURRENT_DATE THEN currbirthday
    ELSE nextbirthday
END

Notes:

  • Today's birthdays appears first regardless of current time
  • February 29 birthday is treated equal to February 28 birthday for common years e.g.
    • On Jan/1/2019 both Feb 28 and Feb 29 birthdays (2019) are sorted equal
    • On Mar/1/2019 Feb 28 and Feb 29 birthdays (2020) are sorted as expected

SQLFiddle

木槿暧夏七纪年 2024-12-10 06:43:22
SELECT name
     , birthday
FROM TableX
ORDER BY DAYOFYEAR(birthday) < DAYOFYEAR(CURDATE())
       , DAYOFYEAR(birthday)

不,由于年份有 366 天,以上可能会产生错误结果。这是正确的:

SELECT name
     , birthday
FROM
  ( SELECT name
         , birthday
         , MONTH(birthday) AS m
         , DAY(birthday) As d
    FROM TableX
  ) AS tmp
ORDER BY (m,d) < ( MONTH(CURDATE()), DAY(CURDATE()) )
       , m
       , d

如果您的表增长到超过数千条记录,那么速度将会非常慢。如果您想要快速查询,请添加包含月份和日期的字段,并在 (bmonth,bday) 上创建索引,或者将它们添加为一个字段,即 Char (08-17 或 0817(对于 8 月 17 日)或 Int(817 对于 8 月 17 日)以及该字段的索引。

SELECT name
     , birthday
FROM TableX
ORDER BY DAYOFYEAR(birthday) < DAYOFYEAR(CURDATE())
       , DAYOFYEAR(birthday)

No, the above may produce error results, due to years with 366 days. This is correct:

SELECT name
     , birthday
FROM
  ( SELECT name
         , birthday
         , MONTH(birthday) AS m
         , DAY(birthday) As d
    FROM TableX
  ) AS tmp
ORDER BY (m,d) < ( MONTH(CURDATE()), DAY(CURDATE()) )
       , m
       , d

If your table grows to more than a few thousands records, it will be real slow. If you want a fast query, add fields with the month and day and have an index on (bmonth,bday) or add them as one field, either Char (08-17 or 0817 for 17-Aug) or Int (817 for 17-Aug) and an index on that field.

離殇 2024-12-10 06:43:22

似乎相当快,闰年没有问题:

SELECT * 
FROM `people` 
ORDER BY CONCAT(SUBSTR(`birthday`,6) < SUBSTR(CURDATE(),6), SUBSTR(`birthday`,6))

Все гениальное -- просто! ;)

Seems to be rather fast, no problems with leap years:

SELECT * 
FROM `people` 
ORDER BY CONCAT(SUBSTR(`birthday`,6) < SUBSTR(CURDATE(),6), SUBSTR(`birthday`,6))

Все гениальное -- просто! ;)

来世叙缘 2024-12-10 06:43:22

不漂亮,但是有用

SELECT * 
,CASE WHEN BirthdayThisYear>=NOW() THEN BirthdayThisYear ELSE BirthdayThisYear + INTERVAL 1 YEAR END AS NextBirthday
FROM (
    SELECT * 
    ,birthday - INTERVAL YEAR(birthday) YEAR + INTERVAL YEAR(NOW()) YEAR AS BirthdayThisYear
    FROM bd
) AS bdv
ORDER BY NextBirthday

Not pretty, but works

SELECT * 
,CASE WHEN BirthdayThisYear>=NOW() THEN BirthdayThisYear ELSE BirthdayThisYear + INTERVAL 1 YEAR END AS NextBirthday
FROM (
    SELECT * 
    ,birthday - INTERVAL YEAR(birthday) YEAR + INTERVAL YEAR(NOW()) YEAR AS BirthdayThisYear
    FROM bd
) AS bdv
ORDER BY NextBirthday
夜还是长夜 2024-12-10 06:43:22

我会这样尝试(但这没有经过测试):

SELECT
  name,
  birthday
FROM
  birthdays
ORDER BY
  ABS( DAYOFYEAR(birthday) - (DAYOFYEAR(CURDATE()) ) ASC

编辑:
将排序从 DESC 更改为 ASC,因为您希望首先获得最远的值,而不是最近的值。

i would try it like this (but this isn't tested):

SELECT
  name,
  birthday
FROM
  birthdays
ORDER BY
  ABS( DAYOFYEAR(birthday) - (DAYOFYEAR(CURDATE()) ) ASC

EDIT:
changed ordering from DESC to ASC because you want to get the farthest first, not the closest.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文