MySQL JOIN查询时如何在另一表无匹配数据时返回收款金额为0

问题原因 #

现有代码使用INNER JOIN关联两张表，仅会保留两张表中invoice_no完全匹配的记录，当发票没有对应收款记录时会被直接过滤，因此无收款记录的项目查询结果为空。

修正方案 #

核心修改点 #

• 将连接方式替换为LEFT JOIN，以invoice_details作为左表，确保指定项目下的所有发票记录都能保留
• 新增IFNULL()函数处理聚合结果，无匹配收款记录时将SUM返回的NULL值转为0.00
• 修正硬编码的项目ID为动态传入参数，使用参数绑定避免SQL注入风险
• 补全GROUP BY的非聚合字段，适配MySQLONLY_FULL_GROUP_BY模式的语法要求

修改后代码 #

public function many($id) {
    $sql = "SELECT 
              invoice_details.invoice_No, 
              invoice_details.invoice_Date, 
              invoice_details.invoice_amount,
              IFNULL(SUM(received_amount_details.received_amount), 0.00) as totalreceiptamt
            FROM invoice_details
            LEFT JOIN (
              SELECT DISTINCT invoice_no, received_amount 
              FROM received_amount_details
            ) AS received_amount_details 
            ON received_amount_details.invoice_no = invoice_details.invoice_No
            WHERE invoice_details.proje_ID = ?
            GROUP BY invoice_details.invoice_No, invoice_details.invoice_Date, invoice_details.invoice_amount";
    // 参数绑定传入项目ID
    return $this->db->query($sql, [$id])->result();
}

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