Php, MySql 连接3张表，并计算SUM

Question

我有 3 个表项目、时间和费用。我想加入所有 3 个并计算总和并检索所有匹配的记录。

这是我的表格:

projects:
id      name
=====================
1       First Project
2       Second Project

times:
id      project_id  hours       billed
===================================================
1       1           2.0         1
2       1           3.0         0
3       2           4.30        0

expense:
id      project_id  amount         billed
==================================================
1       1           120.00         0
2       2           35.00          1
3       2           55.00          0
4       2           45.00          0

这是我的查询:

  SELECT 
    SUM(t.hours) as total_hours,
    SUM(e.amount) as total_amount,
    p.name
  FROM
    `projects` AS p 
    LEFT JOIN `expense` AS e 
      ON e.project_id = p.id
    LEFT JOIN `times` AS t 
      ON t.project_id = p.id 
  WHERE t.billed = 0
  AND e.billed = 0
  GROUP BY p.id;

但由于某种原因我无法让它工作，我最终没有记录。

结果应该是这样的:

Name            Total Hours     Total Expense
==============================================
First Project   3.00             120.00
----------------------------------------------
Second Project  7.30             100.00
----------------------------------------------

Answer 1

您只是使用了错误的表别名:

SELECT 
    SUM(e.hours) as total_hours,
    SUM(t.amount) as total_amount,
    p.name
  FROM
    `projects` AS p 
    LEFT JOIN `expense` e 
      ON e.project_id = p.id
    LEFT JOIN `times` t 
      ON t.project_id = p.id 
  WHERE t.billed = 0
  AND e.billed = 0
  GROUP BY p.id;

按预期工作:http://sqlfiddle.com/#!9/48bfd1/3