如何使用内连接获得两个表值的总和

这可能不是最好的方法，但它会起作用。

SELECT c.ClientNo, c.ClientId, c.FullName, i.InvoiceTotalDue, i.InvoiceTotalBalance, r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance, (i.InvoiceTotalBalance + r.ReturnChequeTotalBalance) AS TotalBalance
FROM Client_TBL c
LEFT JOIN (
SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue, SUM(TotalBalance) AS InvoiceTotalBalance
FROM Invoice_TBL
GROUP BY ClientNo
) AS i ON c.ClientNo = i.ClientNo
LEFT JOIN (
SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue, SUM(TotalBalance) AS ReturnChequeTotalBalance
FROM ReturnCheque_TBL
GROUP BY ClientNo
) AS r ON c.ClientNo = r.ClientNo

当然，您可以将子查询更改为 CTE。但基本上你要在加入前收集数据，否则你可能会得到错误的数据（例如，客户端 2 的发票信息是两倍大，而 ReturnCheque 是三倍大。）

我正在尝试另一种方式，这就是我得到的：

    SELECT 
        ClientNo,
        ClientID,
        FullName,
        SUM(InvoiceTotalDue) as InvoiceTotalDue,
        sum(InvoiceTotalBalance) as InvoiceTotalBalance,
        sum(ReturnChequeTotalDue) as ReturnChequeTotalDue,
        sum(ReturnChequeTotalBalance) as ReturnChequeTotalBalance
    FROM (SELECT 
                CT.ClientNo,
                CT.ClientID,
                CT.FullName, #IT.*,RCT.* 
                IT.TotalDue as InvoiceTotalDue,
                IT.TotalBalance as InvoiceTotalBalance,
                0.00 as ReturnChequeTotalDue,
                0.00 as ReturnChequeTotalBalance  
            FROM Client_TBL as CT
            JOIN Invoice_TBL as IT on (IT.ClientNo=CT.ClientNo)

            UNION
            SELECT 
                CT.ClientNo,
                CT.ClientID,
                CT.FullName, #IT.*,RCT.* 
                0.00 as InvoiceTotalDue,
                0.00 as InvoiceTotalBalance,
                IFNULL((RCT.TotalDue),0.00) as ReturnChequeTotalDue,
                IFNULL((RCT.TotalBalance),0.00) as ReturnChequeTotalBalance   
            FROM Client_TBL as CT
            JOIN ReturnCheque_TBL as RCT on (RCT.ClientNo=CT.ClientNo)) AS ResultSet
    GROUP BY ClientNo;

如果您愿意，您可以创建一个存储过程来过滤您想要的任何信息。