php - SQL Join SUM 无法正常工作

Question

我在生成查询结果时遇到问题。我想生成与主表相关的另一个表的行结果的 SUM。

我输出失败的是payments sum，还有payables sum。

• 问题 #1 - 我无法生成正确的付款输出。这查询似乎产生了一个公式 numrows * sum_of_payments

• 问题 #2 - 我无法生成总应付账款的输出。

我正在使用 Codeigniter 3，这是我的模型。

function fetch_billing_records($case_id, $patient_id, $status) {

        $this->db->join('cases', 'cases.id = billing.case_id', 'left');
        $this->db->join('patients', 'patients.id = cases.patient_id', 'left');            
        $this->db->join('users', 'users.username = billing.user', 'left');
        $this->db->join('billing_payments', 'billing_payments.billing_id = billing.id', 'left');
        $this->db->join('billing_items', 'billing_items.billing_id = billing.id', 'left');
        $this->db->join('services', 'services.title = billing_items.service', 'left');
        $this->db->select('
            billing.id,
            billing.remarks,
            billing.status,
            billing.created_at,
            billing.updated_at,
            users.name as user,
            users.username,
            cases.id as case_id,
            cases.title as case_title,
            patients.id as patient_id,
            CONCAT(patients.lastname, ", ", patients.fullname) as patient_name,
            SUM(billing_payments.amount) as payments,
            SUM((services.amount - billing_items.discount)*billing_items.qty) as payables
        ');

        if(is_int($case_id)) {
            $this->db->where('billing.case_id', $case_id);           
        }

        if(is_int($patient_id)) {
            $this->db->where('patients.id', $patient_id);
        }

        if(is_int($status)) {
            $this->db->where('billing.status', $status);
        }

        $this->db->group_by('billing.id');
        $this->db->where('billing.is_deleted', 0);

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

        log_message('error', $this->db->last_query());
        if ($query->num_rows() > 0) {
            return $query->result_array();
        }

        return false;

}

我用Last Query和Log Helpers保存了Last Query，看来我的实例的查询是

 SELECT `billing`.`id`, `billing`.`remarks`, `billing`.`status`, `billing`.`created_at`, `billing`.`updated_at`, `users`.`name` as `user`, `users`.`username`, `cases`.`id` as `case_id`, `cases`.`title` as `case_title`, `patients`.`id` as `patient_id`, CONCAT(patients.lastname, ", ", patients.fullname) as patient_name, SUM(billing_payments.amount) as payments, SUM((services.amount - billing_items.discount)*billing_items.qty) as payables
FROM `billing`
LEFT JOIN `cases` ON `cases`.`id` = `billing`.`case_id`
LEFT JOIN `patients` ON `patients`.`id` = `cases`.`patient_id`
LEFT JOIN `users` ON `users`.`username` = `billing`.`user`
LEFT JOIN `billing_payments` ON `billing_payments`.`billing_id` = `billing`.`id`
LEFT JOIN `billing_items` ON `billing_items`.`billing_id` = `billing`.`id`
LEFT JOIN `services` ON `services`.`title` = `billing_items`.`service`
WHERE `billing`.`is_deleted` =0

为了更好地指导您解决我目前的问题，这是相关表的架构:

这是整个数据库的架构。

Answer 1

使用 group by 语句。因为 mysql 聚合函数与 group by 配合得很好。如果您想要针对患者的账单金额，请使用以下查询

SELECT 
    `billing`.`id`, 
    `billing`.`remarks`, 
    `billing`.`status`, 
    `billing`.`created_at`, 
    `billing`.`updated_at`, `
    users`.`name` as `user`, 
    `users`.`username`, 
    `cases`.`id` as `case_id`, 
    `cases`.`title` as `case_title`, 
    `patients`.`id` as `patient_id`, 
    CONCAT(patients.lastname, ", ", patients.fullname) as patient_name, 
    SUM(billing_payments.amount) as payments, 
    SUM((services.amount - billing_items.discount)*billing_items.qty) as payables
FROM `billing`
    LEFT JOIN `cases` ON `cases`.`id` = `billing`.`case_id`
    LEFT JOIN `patients` ON `patients`.`id` = `cases`.`patient_id`
    LEFT JOIN `users` ON `users`.`username` = `billing`.`user`
    LEFT JOIN `billing_payments` ON `billing_payments`.`billing_id` = `billing`.`id`
    LEFT JOIN `billing_items` ON `billing_items`.`billing_id` = `billing`.`id`
    LEFT JOIN `services` ON `services`.`title` = `billing_items`.`service`
WHERE `billing`.`is_deleted` =0
GROUP BY patients.id