MySQL左连接关联多表计算项目总工时与总成本的实现方案

数据表结构 #

Project表 #

---------------------------------
project_id   |     name| 
---------------------------------
     1            | project 1  | 
     2            | project 2  | 
---------------------------------

report表 #

----------------------------------------------------
report_id    |     report_emp       | report_hours |report_project_id|   
---------------------------------------------------
  1          |         1            |       5    |        1        | 
  2          |         2            |       7    |        1        | 
  3          |         1            |       9    |        2        | 
  4          |         2            |       6    |        1        | 
  5          |         3            |       8    |        2        | 
--------------------------------------------------

emp表 #

----------------------------------------------
emp_id       |  emp_hourly_cost   |      name  | 
-----------------------------------------------
    1        |         8.5        |       A    | 
    2        |         10         |       B    | 
    3        |         12         |       C    | 
    4        |         9          |       D    | 
    5        |         7.5        |       D    | 
-----------------------------------------------

期望输出 #

---------------------------------------------
     project     |    hours    |  total cost | 
---------------------------------------------
       1         |      18     |       172.5    | 
       2         |      17     |       172.5    | 
---------------------------------------------

原SQL问题说明 #

你提供的SQL存在多处错误：

• 表名、字段名不匹配：projects应为Project，daily_reports_project_id字段不存在，实际对应report_project_id
• 分组逻辑错误：按report_id分组无法得到项目级聚合结果，需按项目ID分组
• 缺少与emp表的关联，无法获取时薪计算总成本

正确SQL语句 #

SELECT 
    p.project_id AS project,
    SUM(r.report_hours) AS hours,
    SUM(r.report_hours * e.emp_hourly_cost) AS `total cost`
FROM 
    Project p
LEFT JOIN 
    report r ON p.project_id = r.report_project_id
LEFT JOIN 
    emp e ON r.report_emp = e.emp_id
GROUP BY 
    p.project_id, p.name
ORDER BY 
    p.project_id;

语句解释 #

1. 表关联：通过Project.project_id关联report.report_project_id，再通过report.report_emp关联emp.emp_id，确保每条工时记录匹配对应员工的时薪
2. 聚合计算：
   • SUM(r.report_hours)：统计每个项目的总工时
   • SUM(r.report_hours * e.emp_hourly_cost)：累加每条记录的工时×时薪，得到项目总成本
3. 分组：按项目ID和名称分组，保证结果以项目为维度聚合

执行该语句即可得到你需要的项目总工时与总成本数据。

内容的提问来源于stack exchange，提问作者hady alzpen