关联两表分组求和计算值过高问题求助

问题根源 #

直接关联production_defects_report和production_gate时，同一工段的多条缺陷记录会与多条生产记录形成笛卡尔积（比如Line2的2条缺陷记录×2条生产记录=4条关联记录），SUM()会重复计算这些重复行，最终导致汇总值远超实际数量。

解决方案：先汇总再关联 #

先分别对缺陷表和生产表按工段+日期做分组汇总，再将汇总结果与工段表关联，从根源避免笛卡尔积。

CodeIgniter3 修正后的模型方法 #

public function fetch_data($limit, $start)
{
    $today = date('Y-m-d');

    // 子查询：预汇总当日各工段的缺陷数量
    $defectSum = $this->db->select('pdr_section_id, SUM(pdr_qty) as defect_qty')
                          ->from('production_defects_report')
                          ->where('DATE(pdr_date)', $today)
                          ->group_by('pdr_section_id')
                          ->get_compiled_select();

    // 子查询：预汇总当日各工段的生产数量
    $productionSum = $this->db->select('pg_sec_id, SUM(pg_qty) as production_qty')
                              ->from('production_gate')
                              ->where('DATE(pg_date)', $today)
                              ->group_by('pg_sec_id')
                              ->get_compiled_select();

    // 主查询：关联工段表与两个汇总子查询
    $this->db->select('s.sec_name, IFNULL(d.defect_qty, 0) as defect_qty, IFNULL(p.production_qty, 0) as production_qty');
    $this->db->from('sections s');
    $this->db->join("($defectSum) d", 's.sec_id = d.pdr_section_id', 'left');
    $this->db->join("($productionSum) p", 's.sec_id = p.pg_sec_id', 'left');
    // 可选：仅保留当日有数据的工段，取消下方注释
    // $this->db->where('(d.defect_qty IS NOT NULL OR p.production_qty IS NOT NULL)');
    $this->db->limit($limit, $start);

    $query = $this->db->get();

    return $query->num_rows() > 0 ? $query->result_array() : false;
}

关键改进点 #

1. 预汇总子查询：每个子查询单独对对应表做分组汇总，确保每个工段仅返回一条汇总记录，彻底消除笛卡尔积。
2. LEFT JOIN 兼容空数据：以工段表为主表关联，确保即使工段当日无缺陷/生产数据也能显示，用IFNULL()将NULL转换为0，保证数值格式统一。
3. 保留分页逻辑：原方法的$limit和$start参数正常生效。

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