如何编写SQL查询统计过去12个月每月分数总和，无数据月份补0用于Chart.js

原代码问题梳理 #

• SQL语法错误：子查询嵌套了非法的SUM查询、日期字段名写错（表中实际为date_created，代码错写为date_added/date）、多处引号缺失
• 逻辑缺陷：没有生成连续月份序列，无法为无数据的月份自动补0
• 安全风险：直接拼接用户ID到SQL语句，存在SQL注入漏洞
• 结果处理错误：直接输出查询结果对象，没有转换为可读取的数组格式

完整实现代码 #

function get_last_12_month_scores(){
    $db = Database::getInstance();
    $mysqli = $db->getConnection();
    $user_id = $_SESSION['user_id'] ?? 0;
    $result = [];

    // 生成过去12个月连续序列 + 关联分数求和的SQL，支持无数据补0
    $sql = "
WITH RECURSIVE months AS (
    -- 取11个月前的第一天作为起始点，覆盖完整12个月周期
    SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 11 MONTH), '%Y-%m-01') as month_date
    UNION ALL
    SELECT DATE_ADD(month_date, INTERVAL 1 MONTH)
    FROM months
    WHERE month_date < DATE_FORMAT(NOW(), '%Y-%m-01')
)
SELECT 
    DATE_FORMAT(m.month_date, '%M') as month_name,
    IFNULL(SUM(s.marks), 0) as total_marks
FROM months m
LEFT JOIN scores s 
    ON DATE_FORMAT(s.date_created, '%Y-%m') = DATE_FORMAT(m.month_date, '%Y-%m')
    AND s.user_id = ?
GROUP BY m.month_date
-- 倒序排列实现最新月份排在结果最前，最旧月份在最后，匹配需求输出顺序
ORDER BY m.month_date DESC
";

    // 用预处理语句防SQL注入
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    $query_result = $stmt->get_result();
    
    // 转换为索引数组，直接支持[i]下标读取
    while($row = $query_result->fetch_assoc()){
        $result[] = $row['total_marks'];
        // 若需要同时返回月份名供Chart.js标签使用，可改成 $result[] = ['month' => $row['month_name'], 'value' => $row['total_marks']];
    }
    $stmt->close();
    return $result;
}

使用说明 #

• 调用get_last_12_month_scores()即可获得长度为12的索引数组，数组顺序和你要求的输出完全一致，每个元素对应当月分数总和
• 若使用的是不支持CTE的MySQL 5.x版本，可替换递归CTE部分为临时表生成连续月份的逻辑即可

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