左连接后如何无重复计算CASE WHEN子句的SUM值？

如何在左连接（left join）后，正确计算CASE WHEN子句中金额的SUM值，避免重复统计？

现有数据表 #

表real #

表target #

错误查询结果（内连接后） #

期望正确结果 #

原尝试的PHP代码（存在重复求和问题） #

<?php
$sql = $pdo->prepare("SELECT *, 
SUM( case when YEAR(real.year) = YEAR(CURDATE())  then real.goal else 0 end) AS goal_now,
SUM( case when YEAR(real.year) = YEAR(CURDATE() - INTERVAL 1 YEAR)  then real.goal else 0 end) AS goal_then,
SUM( case when YEAR(target.year) = YEAR(CURDATE())  then target.goal else 0 end) AS goal_target,
SUM( case when YEAR(target.year) = YEAR(CURDATE() - INTERVAL 1 YEAR) then target.goal else 0 end) AS goal_target_then
FROM real
left join target
on id_real = id_target
group by real.id_real
having
real.id_real LIKE '1%'
");
$sql->execute(); 

while($data = $sql->fetch()){ 

?>

解决方案 #

问题根源 #

直接对原始表进行左连接会产生笛卡尔积：比如real表中ronaldo2022有2条记录，target表中ronaldo2022有1条记录，连接后会生成2条重复行。此时SUM求和会把重复行的数值多次计算，导致结果偏大。

修正思路 #

先分别对real表和target表按id、name进行聚合，提前计算出每个用户每年的真实值和目标值；再将这两个聚合后的结果进行左连接，最后提取对应年份的数据。

修正后的PHP代码 #

<?php
$sql = $pdo->prepare("
    SELECT 
        r.id,
        r.name,
        r.real_now,
        COALESCE(t.target_now, 0) AS target_now,
        r.real_then,
        COALESCE(t.target_then, 0) AS target_then
    FROM (
        -- 先聚合real表，计算每个用户当前年、去年的真实进球总和
        SELECT 
            id,
            name,
            SUM(CASE WHEN year = YEAR(CURDATE()) THEN goal ELSE 0 END) AS real_now,
            SUM(CASE WHEN year = YEAR(CURDATE() - INTERVAL 1 YEAR) THEN goal ELSE 0 END) AS real_then
        FROM real
        GROUP BY id, name
    ) r
    LEFT JOIN (
        -- 先聚合target表，计算每个用户当前年、去年的目标进球总和
        SELECT 
            id,
            name,
            SUM(CASE WHEN year = YEAR(CURDATE()) THEN goal ELSE 0 END) AS target_now,
            SUM(CASE WHEN year = YEAR(CURDATE() - INTERVAL 1 YEAR) THEN goal ELSE 0 END) AS target_then
        FROM target
        GROUP BY id, name
    ) t ON r.id = t.id
    WHERE r.id LIKE '1%'
");

$sql->execute(); 

while($data = $sql->fetch()){ 
    // 处理查询结果，比如输出或赋值
}
?>

这种写法先完成单表聚合，避免了连接后的笛卡尔积问题，求和结果自然准确，同时用COALESCE处理左连接后可能出现的NULL值，保证结果完整性。

内容的提问来源于stack exchange，提问作者rumah dijual bandung