SQL Group By 多字段分组并汇总成绩（年份+科目）

本文详解如何在 php + pdo 环境下，通过 sql 的 `group by` 正确实现按年份（`mark_year`）和科目（`mark_subpid`）双重分组，并对成绩（`mark_mark`）求和；同时指出常见错误及纯 php 补偿方案。

要实现「按学年 + 科目分组并汇总成绩」的效果（如：2018年 maths 总分50，2019年 chemistry 总分110），核心在于 SQL 层面的聚合逻辑必须完整且关联准确。你原查询中存在多个关键问题，导致 GROUP BY 无法正确工作：

❌ 原查询主要问题分析

• 子查询语法错误：select titile,subpid,sub_name, FROM tbl_subject 多了一个逗号，且字段名 titile 拼写错误（应为 title）；
• JOIN 条件缺失/错位：第二个 LEFT JOIN 未限定 m.mark_subpid = s.subpid，也未关联 c.classid 或其他主键，导致笛卡尔积风险；
• GROUP BY 使用不当：仅加 GROUP BY m.mark_year, m.mark_subpid 不够——所有非聚合字段（如 c.classid, s.sub_name, m.mark_year）都必须出现在 GROUP BY 中，或被聚合函数包裹，否则 mysql 严格模式会报错，宽松模式则返回不可靠结果；
• 缺少 SUM() 聚合：SELECT * 与 GROUP BY 冲突，必须显式使用 SUM(m.mark_mark) 并明确选择分组维度字段。

✅ 推荐解决方案：修正 SQL 实现（推荐 ✅）

使用标准 INNER JOIN（因需精确匹配 category=’A’ 及有效关联）+ 显式 GROUP BY + SUM()：

$id = (int)$id; // 防注入：强制整型 $select = $pdo->prepare("     SELECT          c.classid AS `Class ID`,         s.sub_name AS `subject Name`,         m.mark_year AS `Mark year`,         SUM(m.mark_mark) AS `Mark`     FROM tbl_class c     INNER JOIN tbl_subject s ON c.subfkey = s.subpid     INNER JOIN tbl_mark m          ON c.studentfkey = m.mark_studetpid          AND s.subpid = m.mark_subpid     WHERE          c.studentfkey = ?          AND m.mark_category = 'A'     GROUP BY          c.classid,          s.sub_name,          m.mark_year     ORDER BY          m.mark_year DESC,          s.sub_name "); $select->execute([$id]); $results = $select->fetchAll(PDO::FETCH_ASSOC);

✅ 优势：一次查询完成聚合，性能高、逻辑清晰、符合关系数据库设计原则； ⚠️ 注意：确保 tbl_class.classid、tbl_subject.sub_name 在分组中唯一对应（若存在同名科目需加 s.subpid 更稳妥）。

⚠️ 备选方案：PHP 数组聚合（仅限小数据量）

若因历史表结构复杂、无法重构 SQL，可采用 PHP 层聚合（不推荐用于万级数据）：

$stmt = $pdo->prepare("     SELECT          c.classid,          s.sub_name,          m.mark_year,          m.mark_mark     FROM tbl_class c     INNER JOIN tbl_subject s ON c.subfkey = s.subpid     INNER JOIN tbl_mark m          ON c.studentfkey = m.mark_studetpid          AND s.subpid = m.mark_subpid     WHERE c.studentfkey = ? AND m.mark_category = 'A'     ORDER BY m.mark_year DESC "); $stmt->execute([$id]); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);  // PHP 聚合：[classid][sub_name][year] → sum $aggregated = []; foreach ($rows as $row) {     $cid = $row['classid'];     $sub = $row['sub_name'];     $year = $row['mark_year'];     $mark = (float)$row['mark_mark'];      if (!isset($aggregated[$cid][$sub][$year])) {         $aggregated[$cid][$sub][$year] = 0;     }     $aggregated[$cid][$sub][$year] += $mark; }  // 转为扁平数组供模板渲染 $output = []; foreach ($aggregated as $cid => $subjects) {     foreach ($subjects as $sub => $years) {         foreach ($years as $year => $total) {             $output[] = [                 'Class ID'      => $cid,                 'subject Name'  => $sub,                 'Mark year'     => $year,                 'Mark'          => (int)$total             ];         }     } }

? 总结建议

• 优先修复 SQL：用 GROUP BY + SUM() 是标准、高效、可维护的解法；
• *避免 `SELECT 与GROUP BY` 混用**：明确列出分组字段和聚合字段；
• 始终验证 JOIN 条件完整性：每个 JOIN 应有且仅有一个业务逻辑上的关联依据；
• PDO 参数化防注入：勿拼接 $id，务必用 ? 占位符 + execute()；
• 索引优化提醒：为高频查询字段添加联合索引，如 tbl_mark(mark_studetpid, mark_subpid, mark_category, mark_year)。

正确实施后，即可稳定输出结构清晰、按年份与科目聚合的成绩汇总表。