如何将合并表中的多列相加?

I need to write a query that joins several tables and totals several columns in different tables & can't seem to figure out how to do it:

我需要编写一个查询，该查询连接多个表，并在不同的表中列出几列，并且似乎不知道该怎么做:

here are the tables:

这是表:

The result I am trying to get is a result with the contract budgets for a particular budget name added: i.e. [this does not work but gives an idea]

我试图得到的结果是一个特定的预算名称的合同预算的结果:即(这并不奏效，但给出了一个想法)

select c.contract_budget_f1, c.contract_budget_f2, cb.budget_type_id, c.id, sum(cb.budget_f1) as bf1, sum(cb.budget_f2) as bf2
from `flow_contract` c 
left join `flow_contract_budget` cb on cb.contract_id = c.id
where c.program_id = '69'
group by cb.budget_type_id

with the whole result set looking like:

整个结果集如下:

[budget_type_id]    
[contract_budget_f1]
[contract_budget_f2]
[bf1]
[bf2]

where it will return 3 rows with the budgets for each budget type added How can I do this, is it even possible?

它将返回3行，每个预算类型的预算都增加了，我怎么做呢，这是可能的吗?

Here are links to the tables - sorry, didn't realize you couldn't click on them...

这里有一些表格的链接-对不起，没有意识到你不能点击它们…

http://media.bigblockstudios.ca/stack/program-name.gif

http://media.bigblockstudios.ca/stack/program-name.gif

http://media.bigblockstudios.ca/stack/contract-budget.gif

http://media.bigblockstudios.ca/stack/contract-budget.gif

http://media.bigblockstudios.ca/stack/contracts.gif

http://media.bigblockstudios.ca/stack/contracts.gif

UPDATE

更新

I got it working like this:

我让它像这样工作:

    select c.id, c.program_id, c.contract_budget_f1, c.contract_budget_f2, cb.budget_f1, cb.budget_f2, cb.budget_type_id, c.id, 
sum(cb.budget_f1) as bf1, sum(cb.budget_f2) as bf2 
from `flow_contract` c 
left join `flow_contract_budget` cb on cb.contract_id = c.id 
where c.program_id = '".$formfields['program_id']."' 
group by cb.budget_type_id 
order by cb.budget_type_id

2 个解决方案

#1

I think what you want is:

我认为你想要的是:

group by cb.budget_type_id, cb.contract_id

Or the other way around. Could you make an sql fiddle?

或者反过来。你能做一个sql小提琴吗?

#2

Short Answer: subquery

简短的答案是:子查询

Try this: Start with a subquery.

试试这个:从一个子查询开始。

1. Write a query that produces the column values that you want to sum. This is now the subquery.
2. 编写一个查询，生成您想要求和的列值。这是子查询。
3. Write an outter query that sums the desired columns in the inner query.
4. 编写一个outter查询，该查询将在内部查询中包含所需的列。

After you get it working, review it to see if you can optimize out the subquery and just have one query.

在您获得它的工作之后，回顾它，看看您是否能够优化子查询，并且只有一个查询。