我有 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
----------------------------------------------
你只是让表别名错误
: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