MySql JOIN 两个表 SUM 左表的错误结果

Question

我有一个两个表，并且我已离开连接并希望获得两个表中的值之和。
但第二个表的结果未成功检索(SUM 未正确分组)
表测试1

id  redeem_count        cost   camp_id
1   2                   500    1
2   3                   1000   1
3   2                   2000   2
4   2                   3000   2
5   2                   1200   3

表测试2

id  bill_amount     earning test1_id
1   4000            50      1
2   5000            55      3
3   6000            60      4

输出

camp_id redeem  cost    bill    earning
1       5       1500    15000   165
2       4       5000    NULL    NULL
3       2       1200    NULL    NULL

期望的结果

camp_id redeem  cost    bill    earning
1       5       1500    4000    50
2       4       5000    11000   115
3       2       1200    0       0

我已经执行了以下SQL。

SELECT
t1.camp_id,
t1.redeem,
t1.cost,
t2.bill,
t2.earning
FROM (SELECT  COALESCE(SUM(redeem_count),0) AS redeem, COALESCE(SUM(cost),0) AS cost, id,camp_id FROM test1 GROUP BY camp_id) t1
LEFT JOIN(SELECT COALESCE(SUM(bill_amount),0) AS bill, COALESCE(SUM(earning),0) AS earning, test1_id FROM test2) t2
ON t1.id = t2.test1_id

我怎样才能达到期望的结果？请您提供解决方案吗？

Answer 1

SELECT
t1.camp_id,
t1.redeem,
t1.cost,
t2.bill,
t2.earning
FROM (SELECT  COALESCE(SUM(redeem_count),0) AS redeem, COALESCE(SUM(cost),0) AS cost, id,camp_id FROM test1 GROUP BY camp_id) t1
LEFT JOIN(SELECT COALESCE(bill_amount) AS bill, COALESCE(earning) AS earning, test1_id FROM test2) t2
ON t1.id = t2.test1_id