MySQL如何基于另一张表的条件值查询指定列的SUM总和

解决方案 #

直接使用联表聚合查询替换原有循环嵌套查询即可，既可以解决统计逻辑错误问题，也能避免N+1查询带来的性能损耗，SQL逻辑完全匹配你的统计需求：

SELECT 
  g.title, 
  COALESCE(SUM(p.points), 0) AS TotalPoints 
FROM `groups` g
-- 关联得分表
LEFT JOIN points p ON g.id = p.group_id
-- 仅关联已完成的参与记录，过滤未完成的
LEFT JOIN attempts a ON p.attempt_id = a.id AND a.status = 1
-- 按分组聚合计算总得分
GROUP BY g.id, g.title
ORDER BY g.id ASC

对应的PHP实现代码：

$sql = "SELECT 
          g.title, 
          COALESCE(SUM(p.points), 0) AS TotalPoints 
        FROM `groups` g
        LEFT JOIN points p ON g.id = p.group_id
        LEFT JOIN attempts a ON p.attempt_id = a.id AND a.status = 1
        GROUP BY g.id, g.title
        ORDER BY g.id ASC";
$query = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($query)){
    echo $row['title']." : ".$row['TotalPoints'].PHP_EOL;
}

逻辑说明 #

1. 用LEFT JOIN关联attempts表时，把status=1的过滤条件写在JOIN条件中，会直接排除未完成的参与记录对应的得分
2. COALESCE(SUM(p.points), 0)的作用是当某个分组没有符合条件的得分时，返回0而不是NULL，避免输出空值
3. 一次查询就能拿到全部分组的统计结果，不需要循环查询数据库，数据量越大性能优势越明显

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