資料庫
貸款
[id_loan] [id_customer] [qty_loan][amount]
1 1 2 8
2 1 1 4
3 2 3 12
4 1 1 4
支付
[id_pay] [id_customer] [qty_pay] [pay]
1 1 1 4
2 1 2 8
3 2 2 8
4 2 1 4
我想加入然后得到求和結果但結果錯誤這里是sql代碼
SELECT
SUM(qty_loan) as qty_loan,SUM(amount) as amount ,SUM(qty_pay) as qty_pay,SUM(pay) as pay
FROM loan JOIN payment ON loan.id_customer = payment.id_customer
WHERE loan.id_customer='1'
uj5u.com熱心網友回復：
當我們在兩個表中都有 2 條記錄時，JOIN 會找到 4 個匹配項，并且每個值都是重復的。
我們可以在子查詢中查詢其中一張表，按 id_customer 分組，以避免這個問題。
SELECT l.id_customer, qty_loan, amount , SUM(qty_pay) as qty_pay, SUM(pay) as pay FROM (SELECT id_customer, SUM(qty_loan) as qty_loan, SUM(amount) as amount FROM loan GROUP BY id_customer) l LEFT JOIN payment ON l.id_customer = payment.id_customer /*-WHERE loan.id_customer='1'*/ GROUP BY l.id_customer, qty_loan, amount ;id_customer | 數量貸款 | 金額 | 數量_支付 | 支付 ----------: | --------: | -----: | ------: | --: 1 | 4 | 16 | 3 | 12 2 | 3 | 12 | 3 | 12
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/460345.html
上一篇：如何在BigQuery中聯接兩個表，但聯接引數是嵌套的
下一篇：請說明加盟？