mysql - 希望使用 DATETIME 从数据库中选择一行

Question

我有以下查询

SELECT * FROM ".TBL_FOOT_GAMES." ORDER BY id DESC LIMIT 1

我需要在字段 date_confirmed 上添加 WHERE 子句。

date_confirmed 是 DATETIME 类型。

我需要仅选择当前时刻 7 天内的行。

更多代码

SELECT g.home_user, g.away_user, g.home_score, g.away_score, g.id AS gameid, g.date_confirmed, 
       hu.username AS home_username, au.username AS away_username, ht.team AS home_team, at.team AS away_team
       FROM tbl_foot_games g INNER JOIN tbl_users hu ON hu.id = g.home_user INNER JOIN tbl_users au ON au.id = g.away_user 
       INNER JOIN tbl_foot_teams ht ON ht.id = g.home_team INNER JOIN tbl_foot_teams at ON at.id = g.away_team
       WHERE (g.type = '1' OR g.type = '2' OR g.type = '3' OR g.type = '4') AND g.status = '3' AND g.date_confirmed BETWEEN NOW() AND DATE_SUB(NOW(), INTERVAL 50 WEEK)
       ORDER BY g.id DESC LIMIT 1

该语句工作正常，直到我为 50 周间隔添加 WHERE 子句。

Answer 1

假设 future 仅 7 天(看起来您将列出即将举行的足球比赛):

SELECT *
  FROM `tbl`
 WHERE `date_confirmed` BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 WEEK)
 ORDER BY `id` DESC
 LIMIT 1

请阅读the documentation下次先；答案就在那里。