如何在MySQL中按月份SUM多行数据适配ChartJS展示

解决方案 #

你需要在SQL层面直接完成按月聚合计算，不需要读取全表后在PHP侧处理，正确的SQL逻辑是用DATE_FORMAT对日期按月份分组，配合SUM()函数计算每个品类当月总重量。

正确的聚合SQL语句

SELECT
  DATE_FORMAT(`date`, '%b, %Y') as formatted_month,
  SUM(IFNULL(subgrade, 0)) as total_subgrade,
  SUM(IFNULL(castaluminium, 0)) as total_castaluminium,
  SUM(IFNULL(copper, 0)) as total_copper,
  SUM(IFNULL(stainlesssteel, 0)) as total_stainlesssteel,
  SUM(IFNULL(plastic, 0)) as total_plastic,
  SUM(IFNULL(batteries, 0)) as total_batteries,
  SUM(IFNULL(brass, 0)) as total_brass,
  SUM(IFNULL(cables, 0)) as total_cables
FROM daily_recyclables
GROUP BY DATE_FORMAT(`date`, '%Y-%m')
ORDER BY `date` ASC

• 用%Y-%m分组是为了避免不同年份的同一个月被错误合并
• IFNULL是为了处理字段为空时求和结果不会变为NULL的问题
• ORDER BY保证传入ChartJS的月份按时间正序排列

修改后的完整PHP代码

<?php
ob_start();
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// 数据库连接
$db_conx = mysqli_connect("localhost", "ecemsmaster", "ecemsmaster", "ecemsmaster");
// 连接检测
if (mysqli_connect_errno()) {
    echo mysqli_connect_error("Our database server is currently down. : (");
    exit();
}
// 初始化变量
$dates = '';
$subgrades = '';
$castaluminiums = '';
$coppers = '';
$stainlesssteels = '';
$plastics = '';
$batteriess = '';
$brasss = '';
$cabless = '';

// 按月聚合查询
$sql = mysqli_query($db_conx, "
SELECT
  DATE_FORMAT(`date`, '%b, %Y') as formatted_month,
  SUM(IFNULL(subgrade, 0)) as total_subgrade,
  SUM(IFNULL(castaluminium, 0)) as total_castaluminium,
  SUM(IFNULL(copper, 0)) as total_copper,
  SUM(IFNULL(stainlesssteel, 0)) as total_stainlesssteel,
  SUM(IFNULL(plastic, 0)) as total_plastic,
  SUM(IFNULL(batteries, 0)) as total_batteries,
  SUM(IFNULL(brass, 0)) as total_brass,
  SUM(IFNULL(cables, 0)) as total_cables
FROM daily_recyclables
GROUP BY DATE_FORMAT(`date`, '%Y-%m')
ORDER BY `date` ASC
");

while($row = mysqli_fetch_array($sql)){
    $date = $row['formatted_month'];
    $subgrade = $row['total_subgrade'];
    $castaluminium = $row['total_castaluminium'];
    $copper = $row['total_copper'];
    $stainlesssteel = $row['total_stainlesssteel'];
    $plastic = $row['total_plastic'];
    $batteries = $row['total_batteries'];
    $brass = $row['total_brass'];
    $cables = $row['total_cables'];

    $dates .= '"'.$date.'",';
    $subgrades .= $subgrade.',';
    $castaluminiums .= $castaluminium.',';
    $coppers .= $copper.',';
    $stainlesssteels .= $stainlesssteel.',';
    $plastics .= $plastic.',';
    $batteriess .= $batteries.',';
    $brasss .= $brass.',';
    $cabless .= $cables.',';
}
// 去掉末尾多余的逗号
$dates = trim($dates, ",");
$subgrades = trim($subgrades, ",");
$castaluminiums = trim($castaluminiums, ",");
$coppers = trim($coppers, ",");
$stainlesssteels = trim($stainlesssteels, ",");
$plastics = trim($plastics, ",");
$batteriess = trim($batteriess, ",");
$brasss = trim($brasss, ",");
$cabless = trim($cabless, ",");
?>

如果需要筛选特定时间段的统计数据，在FROM daily_recyclables后加WHERE条件过滤日期即可。

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