PHP+MySQL实现年份列去重并累加对应数值列的查询方法

实现方案 #

你要的按年份去重并合并同年份数值的需求，直接在SELECT查询中用GROUP BY分组搭配SUM()聚合函数即可实现，不需要额外在PHP层做循环处理，执行效率更高。

修改后的SQL查询 #

把原来的全量查询改成分组聚合查询，语句如下：

SELECT `date`, SUM(`numbers`) AS total_numbers 
FROM years 
WHERE `date` BETWEEN 1990 AND 1993
GROUP BY `date`;

说明：GROUP BY date会把相同年份的行合并为一组，SUM(numbers)会计算每组内数值的总和，取别名total_numbers方便后续调用。

完整PHP代码示例 #

<?php
// 预处理查询
$test = $conn->prepare("SELECT `date`, SUM(`numbers`) AS total_numbers FROM years WHERE `date` BETWEEN 1990 AND 1993 GROUP BY `date`");
$test->execute();
// 取回结果
$res = $test->fetchAll(PDO::FETCH_ASSOC);

// 遍历输出
foreach($res as $item){
    echo $item['date'] . ' ' . $item['total_numbers'] . '<br>';
}
?>

补充说明 #

如果后续需要动态传入年份范围，不要直接拼接SQL字符串，建议用PDO参数绑定避免SQL注入风险，写法参考：

$startYear = 1990;
$endYear = 1993;
$test = $conn->prepare("SELECT `date`, SUM(`numbers`) AS total_numbers FROM years WHERE `date` BETWEEN ? AND ? GROUP BY `date`");
$test->execute([$startYear, $endYear]);

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