带条件的两行SQL计算：发票尾款统计与CodeIgniter代码优化

• 现有invoices表，包含sum（金额）、invoice_id（发票ID）字段，invoice_id值对应其他发票ID的记录为预付款项（例：发票1567是预付款，对应最终发票1551，两者合计应统计为2902.75）
• 当前CodeIgniter函数直接汇总金额得到错误结果4063.65，需调整SQL逻辑实现正确统计，且不修改原有数据

修改后的PHP函数代码 #

/**
** Get sum of late
** return object
**/
public static function latePayments($option="")
{
    $baseWhere = "
        estimate != 1 
        AND due_date < CURDATE()
    ";
    
    $statusWhere = "";
    if ($option !== "all") {
        $statusWhere = "
            AND (status = 'Open' OR status = 'Sent' OR status = 'PartiallyPaid')
        ";
    }

    $sql = "
        SELECT 
            SUM(i.`sum` - COALESCE(p.pre_sum, 0)) as `summary`
        FROM invoices i
        LEFT JOIN (
            SELECT 
                invoice_id as target_invoice_id,
                SUM(`sum`) as pre_sum
            FROM invoices
            WHERE EXISTS (
                SELECT 1 FROM invoices main_inv 
                WHERE main_inv.invoice_id = invoices.invoice_id 
                AND main_inv.invoice_id != invoices.invoice_id
            )
            GROUP BY invoice_id
        ) p ON i.invoice_id = p.target_invoice_id
        WHERE 
            NOT EXISTS (
                SELECT 1 FROM invoices pre_inv 
                WHERE pre_inv.invoice_id = i.invoice_id 
                AND pre_inv.invoice_id != i.invoice_id
            )
            AND {$baseWhere}
            {$statusWhere}
    ";

    $result = Invoice::find_by_sql($sql);
    return $result[0]->summary ?? 0;
}

核心逻辑说明 #

1. 预付款汇总子查询：通过EXISTS判断预付款记录（invoice_id指向其他发票的记录），按目标发票ID分组，计算每个发票对应的总预付款金额
2. 主发票金额计算：仅统计非预付款发票，用LEFT JOIN关联对应预付款总额，通过COALESCE处理无预付款的发票（避免NULL值影响求和）
3. 保留原有筛选规则：保留原有的estimate !=1、逾期日期判断，以及option="all"时的状态筛选逻辑
4. 空值处理：加入?? 0确保无符合条件数据时返回0，避免报错

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