我正在尝试使用下面的查询使简单的MySQL查询显示即将到来的生日。如何从显示中排除/删除前一天(昨天)。

CREATE TABLE users (
  name VARCHAR(100),
  birthday DATE
);
INSERT INTO users (name, birthday) VALUES
  ('kostas',  '1983-10-08'),
  ('kostas',  '1983-10-11'),
  ('yannis',  '1979-10-13'),
  ('natalia', '1980-10-15'),
  ('kostas',  '1983-10-12'),
  ('Moskas',  '1978-10-14'),
  ('Rasman',  '1978-10-13'),
  ('natalia', '1980-10-18'),
  ('natalia', '1980-10-16');


查询:

SELECT *
FROM
  users
WHERE
  birthday != '' AND ABS(DAY(CURDATE()) - DAY(birthday)) < 2
ORDER BY
  DAY(birthday)


演示:sqlfiddle

最佳答案

您必须使用BETWEEN而不是ABS。绝对值不会返回您想要的值,0到“生日前的天数”(2)之间是获取生日前天数的正确方法。

您还必须使用DAYOFYEAR而不是DAY,并且必须颠倒减法项的顺序DAYOFYEAR(birthday) - DAYOFYEAR(CURDATE())

若要解决leap年生日,如建议的here,生日年应通过以下方式转换为当年:

DAYOFYEAR(DATE_ADD(e.birthdate, INTERVAL (YEAR(NOW()) - YEAR(birthday)) YEAR))


最终的SQL是:

SELECT *
FROM
  users
WHERE
  birthday != '' AND (DAYOFYEAR(DATE_ADD(birthday, INTERVAL (YEAR(NOW()) - YEAR(birthday)) YEAR))-DAYOFYEAR(CURDATE())) between 0 and 2
ORDER BY
  DAY(birthday)

关于mysql - 用户即将到来的x天生日,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/46727039/

10-12 00:14