MySQL获取具有相同id的表的总和

Question

我有四个表个人、贷款、ca、付款

我想获得所有付款金额和现金预付款金额的总和，这些金额与从特定日期起与某人加入的贷款具有相同的ID。

这是我的代码，但总和计算不正确：

SELECT pd.*,
    l.total_loan_amount,
    sum(c.ca_total_amount) AS ctot,
    sum(p.payment_amount)
FROM personal_data pd
LEFT JOIN loans l
    ON pd.id_personal_data = l.id_personal_data
LEFT JOIN ca c
    ON l.id_loan = c.id_loan
LEFT JOIN payments p
    ON l.id_loan = p.id_loan
WHERE l.loan_date = curDate()
    AND (
        c.ca_date = curDate()
        OR c.ca_date IS NULL
        )
    AND (
        p.payment_date = curDate()
        OR p.payment_date IS NULL
        )
GROUP BY pd.id_personal_data

Answer 1

这样做有时可能检索到无效的结果，因为id有时可能出现在另一个表上，也可能不出现。

尝试为要检索的每个列使用子查询。

SELECT  pd.*,
        l.total_loan_amount,
        c.totalCA,
        p.totalPayment
FROM    personal_data pd
        LEFT JOIN loans l
            ON pd.id_personal_data = l.id_personal_data
        LEFT JOIN
        (
            SELECT  id_loan, SUM(ca_total_amount) totalCA
            FROM    ca 
        --  WHERE   DATE(ca_date) = DATE(CURDATE()) OR
        --          ca_date IS NULL
            GROUP BY id_loan
        ) c ON l.id_loan = c.id_loan
        LEFT JOIN
        (
            SELECT  id_loan, SUM(payment_amount) totalPayment
            FROM    payments 
        --  WHERE   DATE(payment_date) = DATE(CURDATE()) OR
        --          payment_date IS NULL
            GROUP BY id_loan
        ) p ON l.id_loan = p.id_loan
WHERE DATE(l.loan_date) = DATE(curDate())

我认为每笔付款和现金预付款的日期都是不相关的，因为您是根据贷款日期来查找总额的