通过部门的总金额（包括公司）(Total Amount Via Sector (Which include companies))

我试图运行一个包含三个表的SQL语句。

收入

-id -title -amount -company_id

公司

-id -name -sector_id

行业

-id -name

正如您所看到的，“收入”表与“行业”表没有直接关系。我想做的是通过IndustrySectors获得总收入。

到目前为止，我已经设法计算了两张表格，但知识仅限于三张。请引导我。

这是两张桌子。按公司收入

$x=0; foreach($companies as $Company){ $companyName= $Company['Company']['name']; $companyId= $Company['Company']['id']; $query= $this->Income->query(" SELECT SUM( amount ) AS IncomeTotal FROM incomes WHERE company_id=$companyId "); $total=$query[0][0]['IncomeTotal']; if($total!=null){ //$incomeByCompany[$companyName]=$total; //$incomeByCompany['total']=$total; $incomeByCompany[$x]['companyId']=$companyId; $incomeByCompany[$x]['name']=$companyName; $incomeByCompany[$x]['total']=$total; } $x++; }

I am trying to run a SQL statement that includes three tables.

incomes

-id -title -amount -company_id

companies

-id -name -sector_id

sectors

-id -name

as you can see 'incomes' table is not directly related with 'sectors' table. What I am trying to do is to get total of income by IndustrySectors.

I had managed to calculate two tables so far but knowledge is limited for three. Please kindly guide me.

this is for two tables.Income By Company

$x=0; foreach($companies as $Company){ $companyName= $Company['Company']['name']; $companyId= $Company['Company']['id']; $query= $this->Income->query(" SELECT SUM( amount ) AS IncomeTotal FROM incomes WHERE company_id=$companyId "); $total=$query[0][0]['IncomeTotal']; if($total!=null){ //$incomeByCompany[$companyName]=$total; //$incomeByCompany['total']=$total; $incomeByCompany[$x]['companyId']=$companyId; $incomeByCompany[$x]['name']=$companyName; $incomeByCompany[$x]['total']=$total; } $x++; }

最满意答案

SELECT s.name , SUM(i.amount) as total_sector FROM sectors s JOIN companies c ON s.id = c.sector_id JOIN incomes i ON c.id = i.company_id GROUP BY s.name

有意思的是，你的公司查询应该是：

SELECT c.name , SUM(i.amount) as total_sector FROM companies c ON s.id = c.sector_id JOIN incomes i ON c.id = i.company_id GROUP BY c.name SELECT s.name , SUM(i.amount) as total_sector FROM sectors s JOIN companies c ON s.id = c.sector_id JOIN incomes i ON c.id = i.company_id GROUP BY s.name

Aditionally instead of a loop your company query should be:

SELECT c.name , SUM(i.amount) as total_sector FROM companies c ON s.id = c.sector_id JOIN incomes i ON c.id = i.company_id GROUP BY c.name

更多推荐