mysql - 如何在 where 子句中设置 2 个以上的条件？

Question

我在 MySQL 数据库比赛、锦标赛和国家/地区中有三个表。我想要这三个表中的列，这些列在查询的日期范围内(两天之间):

SELECT matches.tournament_id 't_id',
       matches.localteam_ft_score,
       matches.visitorteam_ft_score,
       matches.match_time,
       matches.match_status,
       matches.match_date,
       matches.localteam_id,
       matches.visitorteam_id,
       matches.match_id,
       matches.id,
       matches.static_id,
       matches.localteam_name,
       matches.visitorteam_name,
       matches.halftime_score,
       tournaments.tournament_id,
       tournaments.league_link,
       tournaments.full_league_tr,
       countries.country_name
FROM matches
INNER JOIN tournaments ON tournaments.id = matches.tournament_id
INNER JOIN countries ON tournaments.country_id = countries.country_id
WHERE match_status IN('AET','FT','Pen.','Awarded')
  AND countries.country_name='Worldcup'
  AND matches.match_date BETWEEN '19.05.2013' AND '19.06.2013'

问题是:我无法获取这两个日期之间的记录，它只是给我与“19.05.2013”​​日期的匹配我尝试了很多方法来解决它但没有任何效果。我想知道以这种方式做三个条件是否正确？这是获取 2 个日期之间的记录的正确方法吗？

Answer 1

根据您上面的评论，您需要将 match_date 字段的数据类型更改为 DATE (YYYY-MM-DD)。 MySQL 无法对字符串执行 between 操作。为此，您需要对要从字符串格式转换为日期格式的表列使用 STR_TO_DATE 函数。

  SELECT matches.tournament_id 't_id', matches.localteam_ft_score,
   matches.visitorteam_ft_score, matches.match_time, matches.match_status,
   matches.match_date, matches.localteam_id, matches.visitorteam_id,
   matches.match_id, matches.id, matches.static_id,matches.localteam_name,
   matches.visitorteam_name, matches.halftime_score,  tournaments.tournament_id,
   tournaments.league_link, tournaments.full_league_tr, countries.country_name 
  FROM matches 
  INNER JOIN tournaments ON tournaments.id = matches.tournament_id 
  INNER JOIN countries ON tournaments.country_id = countries.country_id 
  WHERE match_status IN('AET','FT','Pen.','Awarded') 
  AND countries.country_name='Worldcup' AND STR_TO_DATE(matches.match_date,'%d.%m.%Y') between '2013.05.19' and '2013.06.19'