mysql - 如何获得表的总和并减去其他表的值？

Question

我有 2 张 table :

预算

id | project_name | total_budget
1  |    test      | 5000
2  |    try       | 200

费用

id|  project_name |paper|ballpen|total
1 | test          |100  |200    | 300
2 | test          |50   |50     |100
3 | try           |20   |0      |20

所以测试project_name的总数是400

5000 - 400 = 4600 

尝试 200 -20 = 180我想要这个结果

抱歉，我是新手，我无法得到这个

Answer 1

这是一个简单的聚合连接:

select b.project_name, b.total_budget, e.total as total_expense, b.total_budget - e.total as diff
from budget b left join
     (select project_name, sum(total) as total
      from expense e
      group by project_name
     ) e
     on b.project_name = e.project_name;

如果您希望将其用于一个project_name，只需添加一个where 子句:

where b.project_name = 'test'