SQL年月范围查询逻辑错误求助：筛选结果不符合预期

现有month和year两列，查询语句在特定参数下返回结果不符合预期：

• 当选择时间范围为4/2020到12/2022时，期望返回2020年4月至2022年12月的所有数据，但当前查询会移除每年的前4个月数据。
• 示例：查询1/2020到12/2022时能返回正确全量数据；但查询4/2020到12/2022时，错误过滤掉了2021年全量及2022年3月数据，仅保留部分结果。

原错误代码 #

错误逻辑SQL #

SELECT r.country_id, r.rate as rate, r.month, d.country_id, d.year, d.month as value 
FROM `data_prod` d 
INNER JOIN monthly_data r ON r.country_id = d.country_id AND r.year=d.year AND r.month=d.month 
WHERE d.country_id IN (160) 
AND d.year BETWEEN 2020 AND 2022 
AND r.year BETWEEN 2020 AND 2022 
AND d.month BETWEEN 4 AND 12 
ORDER BY d.year, d.month

原PHP拼接代码 #

$countryIds = $countries;
$month_clause = "AND d.month BETWEEN $selectedStartMonth AND $selectedEndMonth";
        
$query = "SELECT r.country_id, r.rate as rate, r.month, d.country_id, d.year, d.month as value
      FROM `data_prod` d
      INNER JOIN monthly_data r ON r.country_id = d.country_id AND r.year=d.year AND r.month=d.month 
      WHERE d.country_id IN (".implode(",",$countryIds).") 
      AND d.year BETWEEN $periodStart AND $periodEnd
      AND r.year BETWEEN $periodStart AND $periodEnd
      $month_clause
      ORDER BY d.year, d.month";

问题根源 #

原代码用d.month BETWEEN 起始月 AND 结束月对所有年份的月份做统一过滤，导致非起始年份的前几个月也被错误排除（比如2021年的1-3月、2022年的1-3月）。正确逻辑需要分年份处理时间范围：

1. 起始年份：月份≥起始月份
2. 中间年份：保留所有月份
3. 结束年份：月份≤结束月份

修正后的代码 #

修正逻辑示例SQL #

SELECT r.country_id, r.rate as rate, r.month, d.country_id, d.year, d.month as value 
FROM `data_prod` d 
INNER JOIN monthly_data r ON r.country_id = d.country_id AND r.year=d.year AND r.month=d.month 
WHERE d.country_id IN (160) 
AND (
    (d.year = 2020 AND d.month >= 4)
    OR (d.year > 2020 AND d.year < 2022)
    OR (d.year = 2022 AND d.month <= 12)
)
AND r.year BETWEEN 2020 AND 2022 
ORDER BY d.year, d.month

修正后的PHP拼接代码 #

$countryIds = $countries;
// 动态生成时间范围条件
$date_condition = "";
if ($periodStart == $periodEnd) {
    // 同一年份的情况
    $date_condition = "AND (d.year = $periodStart AND d.month BETWEEN $selectedStartMonth AND $selectedEndMonth)";
} else {
    $date_condition = "AND (";
    // 起始年份：月份≥开始月
    $date_condition .= "(d.year = $periodStart AND d.month >= $selectedStartMonth)";
    // 中间年份：保留所有月份（如果存在中间年份）
    if ($periodEnd - $periodStart > 1) {
        $date_condition .= " OR (d.year > $periodStart AND d.year < $periodEnd)";
    }
    // 结束年份：月份≤结束月
    $date_condition .= " OR (d.year = $periodEnd AND d.month <= $selectedEndMonth)";
    $date_condition .= ")";
}
        
$query = "SELECT r.country_id, r.rate as rate, r.month, d.country_id, d.year, d.month as value
      FROM `data_prod` d
      INNER JOIN monthly_data r ON r.country_id = d.country_id AND r.year=d.year AND r.month=d.month 
      WHERE d.country_id IN (".implode(",",$countryIds).") 
      AND r.year BETWEEN $periodStart AND $periodEnd
      $date_condition
      ORDER BY d.year, d.month";

额外提醒 #

直接将变量拼接进SQL存在SQL注入风险，建议使用PHP的PDO或mysqli的参数绑定功能替代字符串拼接，提升代码安全性。

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