如何在PHP中实现MySQL SUM统计值与电子垃圾回收目标值的差值计算

嘿，刚好能帮你搞定这个需求！要算出当前电子垃圾回收总量和12000KG目标的差距，有两种简单的实现方式，你可以根据自己的习惯选：

方案一：在SQL语句中直接计算差值 #

这种方式让数据库直接帮你算出结果，PHP只需要取数就行，代码更简洁：

<h3>目标: 12000KG</h3>
<h3>当前值: 
<?php 
$dbConnection = new PDO('mysql:dbname=ecemsmaster;host=127.0.0.1;charset=utf8', 'ecemsmaster', 'ecemsmaster');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 修改SQL，直接计算总和与目标的差值
$sql = "SELECT 
    MONTH(date), 
    SUM(subgrade + castaluminium + copper + stainlesssteel + plastic + brass + batteries + cables + lowgradePCB + mediumgradePCB + highgradePCB) AS total_weight,
    (SUM(subgrade + castaluminium + copper + stainlesssteel + plastic + brass + batteries + cables + lowgradePCB + mediumgradePCB + highgradePCB) - 12000) AS target_difference
    FROM daily_recyclables 
    WHERE MONTH(date)=MONTH(curdate())";

$stmt = $dbConnection->prepare($sql);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

// 输出当前总重量
echo $row['total_weight']; 
?> KG</h3>
<h3><?php 
// 根据差值正负显示更友好的文案
if ($row['target_difference'] > 0) {
    echo '超出目标: **' . $row['target_difference'] . 'KG**';
} elseif ($row['target_difference'] < 0) {
    // 把负数转成正数，员工更容易理解还差多少
    echo '未达目标: **' . abs($row['target_difference']) . 'KG**';
} else {
    echo '刚好达成目标! 🎉';
}
?></h3>

方案二：在PHP代码中计算差值 #

如果你更倾向于把逻辑放在PHP里处理，保持SQL只负责统计总和，也很灵活：

<h3>目标: 12000KG</h3>
<h3>当前值: 
<?php 
$dbConnection = new PDO('mysql:dbname=ecemsmaster;host=127.0.0.1;charset=utf8', 'ecemsmaster', 'ecemsmaster');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "SELECT 
    MONTH(date), 
    SUM(subgrade + castaluminium + copper + stainlesssteel + plastic + brass + batteries + cables + lowgradePCB + mediumgradePCB + highgradePCB) AS total_weight
    FROM daily_recyclables 
    WHERE MONTH(date)=MONTH(curdate())";

$stmt = $dbConnection->prepare($sql);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

$totalWeight = $row['total_weight'];
// 在PHP里计算与目标的差值
$targetDifference = $totalWeight - 12000;

echo $totalWeight; 
?> KG</h3>
<h3><?php 
if ($targetDifference > 0) {
    echo '超出目标: **' . $targetDifference . 'KG**';
} elseif ($targetDifference < 0) {
    echo '未达目标: **' . abs($targetDifference) . 'KG**';
} else {
    echo '刚好达成目标! 🎉';
}
?></h3>

两种方案都能实现你的需求，方案一减少PHP的计算量，方案二更方便后续调整逻辑。我还加了正负值的判断，让显示的文案更贴合实际场景，员工一眼就能看懂当前状态~

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