如何在含INNER JOIN的SQL语句中使用Between按年份筛选Entry_Date字段

• 你只需在现有WHERE子句中新增Entry_Date的BETWEEN过滤条件即可，无需修改原有JOIN关联逻辑
• 优先推荐使用完整日期范围做筛选，避免对Entry_Date字段做函数运算，可保留字段的索引有效性

场景1：Entry_Date为日期类型字段 #

直接用完整日期区间筛选，假设传入的起始年份变量为$startYear，结束年份变量为$endYear，修改后的SQL如下：

SELECT Patients.Code, Patients.Name, Patients.FirstName 
FROM Patients 
INNER JOIN Pays ON Patients.CountryCode = CountryCode.Code
INNER JOIN Sex ON Patients.Sex = Sex.Code
INNER JOIN ReasonCode ON Patients.ReasonCode = ReasonCode.Code
WHERE Patients.Name LIKE  '".$namePatient."%'
  AND Patients.ReasonCode LIKE '".$ReasonCode."%'
  AND Patients.CountryCode LIKE '".$CountryCode."%'
  AND Patients.Entry_date BETWEEN '".$startYear."-01-01' AND '".$endYear."-12-31'
ORDER BY Patients.Name ASC

场景2：Entry_Date为字符串类型（存储格式为DD/MM/YYYY） #

需要先将字符串转为日期类型再做比较，以MySQL为例：

SELECT Patients.Code, Patients.Name, Patients.FirstName 
FROM Patients 
INNER JOIN Pays ON Patients.CountryCode = CountryCode.Code
INNER JOIN Sex ON Patients.Sex = Sex.Code
INNER JOIN ReasonCode ON Patients.ReasonCode = ReasonCode.Code
WHERE Patients.Name LIKE  '".$namePatient."%'
  AND Patients.ReasonCode LIKE '".$ReasonCode."%'
  AND Patients.CountryCode LIKE '".$CountryCode."%'
  AND STR_TO_DATE(Patients.Entry_date, '%d/%m/%Y') BETWEEN '".$startYear."-01-01' AND '".$endYear."-12-31'
ORDER BY Patients.Name ASC

注意：当前SQL直接拼接PHP变量存在SQL注入风险，建议替换为预处理语句绑定参数，提升安全性。

内容的提问来源于stack exchange，提问作者Kyrian Kyky