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

Question

我有四个表person,loan,ca,payments

我想获得所有付款金额和预付现金金额的总和，这些金额与特定日期与某个人的贷款具有相同 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())

我认为每笔付款和预付现金的日期是无关紧要的，因为您要根据贷款日期

查找其总数