mysql - 将多个表与一个主表连接起来

Question

我有六个表 - Project、Equipment、Fish、Staff 和连接表 - Project_Equipment、Project_Fish 和 Project_Staff。我想检索项目总成本。
所以，我写了如下声明，

SELECT P.ProjectID, (SUM(E.EquipPrice*PE.EQuantity)+SUM(F.FishPrice*PF.FQuantity)+SUM(PS.Salary)) as ProjectCost  
FROM Equipment as E INNER JOIN Project_Equipment as PE  
ON E.EquipID=PE.EquipID  
INNER JOIN Project as P  
ON PE.ProjectID=P.ProjectID  
INNER JOIN Project_Fish as PF  
ON P.ProjectID=PF.ProjectID  
INNER JOIN Fish as F  
ON PF.FishID=F.FishID  
INNER JOIN Project_Staff as PS  
ON P.ProjectID=PS.ProjectID  
INNER JOIN Staff as S
PS.StaffID=S.StaffID  
GROUP BY ProjectID  

但是，我得到的价格是正确金额的两倍。

Answer 1

您的查询最终会得到大量重复结果。考虑以下更简单的情况:

Table: Project
ProjectID  EQuantity
  1          1
  2          1

Table: Equipment
EquipID    EPrice
  1          1
  2          1

Table: Fish
FishID
  1
  2

Table: Project_Equipment
ProjectID  EquipID
  1          1
  1          2
  2          1
  2          2

Table: Project_Fish
ProjectID  FishID
  1          1 
  1          2
  2          1
  2          2

现在，我们只看一下 Project_Equipment 查询的结果:

SELECT p.projectid, e.eprice, pe.equantity FROM project p
INNER JOIN project_equipment pe ON pe.projectid=p.projectid
INNER JOIN equipment e ON e.equipid=pe.equipid

ProjectID EPrice EQuantity
1    1    1   // a
1    1    1   // b
2    1    1   // c
2    1    1   // d

正如预期的那样；每个项目使用的每台设备的价格和数量 list 。但是，当我们 INNER JOIN 到 project_fish 时，您认为会发生什么？第一个结果中有项目 1 两次，项目 2 两次，因此我们最终得到该结果和 project_fish 的每种组合!

SELECT p.projectid, e.eprice, pe.equantity, f.fishid FROM project p
INNER JOIN project_equipment pe ON pe.projectid=p.projectid
INNER JOIN equipment e ON e.equipid=pe.equipid
INNER JOIN project_fish pf ON pf.projectid=p.projectid

ProjectID EPrice EQuantity FishID
1    1    1    1   // from a above
1    1    1    2   // from a above
1    1    1    1   // from b above 
1    1    1    2   // from b above
2    1    1    1   // from c above
2    1    1    2   // from c above
2    1    1    1   // from d above
2    1    1    2   // from d above

这种复制将在每次内部连接时继续。您的价格折扣金额并不总是 2 倍，它实际上取决于所有连接的组合数量。

因此，您无法真正对这个特定查询执行您想要执行的操作。相反，您必须单独计算每个关系的成本。然后你将所有这些加在一起。您可以通过单独选择每个项目并将成本计算到 ProjectID 和 ProjectCost 列中，使用 UNION 将它们连接在一起，然后一次来完成此操作再次按 ProjectID 对结果进行分组，并对各个 ProjectCost 小计进行求和。

我对此的解释很糟糕，但可以将其视为对设备、鱼类和工资成本进行小计，然后将所有这些小计放入一张表中并对其进行求和。例如:

SELECT x.projectid, SUM(x.ProjectCost) FROM 
(
    SELECT p.projectid, SUM(e.eprice * pe.equantity) ProjectCost FROM project p
    INNER JOIN project_equipment pe ON pe.projectid=p.projectid
    INNER JOIN equipment e ON e.equipid=pe.equipid
    GROUP BY p.projectid
  UNION
    SELECT p.projectid, SUM(f.fprice * pf.fquantity) ProjectCost FROM project p
    INNER JOIN project_fish pf ON pf.projectid=p.projectid
    INNER JOIN fish f ON f.fishid=pf.fishid
    GROUP BY p.projectid
  UNION
    SELECT p.projectid, SUM(s.salary) ProjectCost FROM project p
    INNER JOIN project_staff ps ON ps.staffid=p.projectid
    INNER JOIN staff s ON s.staffid=ps.staffid
    GROUP BY p.projectid
) x
GROUP BY x.projectid

每个子查询都会生成一个 projectid 列和一个 ProjectCost 列。单独运行子查询(在括号之间)以查看结果。然后，外部查询添加项目的小计。

抱歉，顺便说一句，我在测试时将您的 EquipPrice 和 FishPrice 列重命名为 EPrice 和 FPrice。