mysql - 多个左连接问题

Question

$sth = $db->prepare("SELECT tblCompanies.*, users.Username, cargo.Name, (tblCompanies.Money + SUM(tblCTerminals.sellValue)) as assets, (COUNT(tblFinishedContracts.ID) + COUNT(tblFinishedSubContracts.ID)) as completed FROM ((((tblCompanies LEFT JOIN users ON tblCompanies.CompanyCEO = users.ID) LEFT JOIN cargo ON (tblCompanies.PreferredCargo = cargo.Cargo_ID)) LEFT JOIN tblCTerminals ON (tblCompanies.Company_ID = tblCTerminals.companyID)) LEFT JOIN tblFinishedContracts ON (tblCompanies.Company_ID = tblFinishedContracts.companyID)) LEFT JOIN tblFinishedSubContracts ON (tblCompanies.Company_ID = tblFinishedSubContracts.companyID) WHERE (users.Username LIKE :info || tblCompanies.CompanyName LIKE :info2 || CONCAT('$',FORMAT((tblCompanies.Money + SUM(tblCTerminals.sellValue)),2)) LIKE :info3 || CONCAT('$',FORMAT(tblCompanies.Money,2)) LIKE :info4 || cargo.Name LIKE :info5 || users.pLevel LIKE :info6 || CONCAT('$',FORMAT((users.Cash_In_Bank + users.Cash_In_Hand),2)) LIKE :info7)");

$sth->bindValue(':info', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info2', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info3', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info4', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info5', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info6', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
$sth->bindValue(':info7', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);

好吧，现在我对这个困惑的推理:

我正在使用jQuery Datatables插件，当它向服务器发出请求时，它会发送查询。它期望返回结果、搜索查询中有多少结果以及总结果有多少。通过这个查询我可以返回结果。

但是在 COUNT(tblFinishedContracts.ID) 上，它抓取了表 8 次。例如，它不是返回正确的数字 8，而是返回计数 112。

当我这样做时:

SELECT COUNT(tblFinishedContracts.ID) 
FROM tblCompanies 
LEFT JOIN tblFinishedContract 
     ON (tblCompanies.Company_ID = tblFinishedContracts.ID)     
WHERE tblCompanies.Company_ID = 11

它有效，它返回 8。如果有人知道更好的方法来完成我想要完成的任务，或者可以告诉我如何解决问题，我将不胜感激!

谢谢

杰夫

编辑:添加，没有列有重复的名称，一切都不同。

Answer 1

对于这个问题没有单一的 Elixir ；获取有关父表的信息，同时从具有一对多关系的多个子表获取信息总是很棘手。但有一些技巧可以提供帮助。

首先，除了从中获取记录计数之外，您实际上并没有对 tblFinishedContracts 执行任何操作；因此，您可以使用子查询，而不是编写 COUNT(tblFinishedContracts.ID) 并使用联接:编写 (SELECT COUNT(1) FROM tblFinishedContracts WHERE Company_ID = tblCompanies.Company_ID)。同样的情况也适用于 tblFinishedSubContracts，因此您也可以消除该连接。 (顺便说一句，消除这些联接也应该显着提高您的性能，因为您只需要检索属于实际返回的公司的契约(Contract)信息。)

这可以解决大部分问题。加入 users 和 cargo 以获得 CEO 和首选 cargo 应该不会引起任何问题。 (对吗？)

剩下的唯一困难是 tblCTerminals，因为 SUM(tblCTerminals.sellValue) 字段同时出现在字段列表和 WHERE 子句中。没有真正令人信服的方法来处理它。一种选择是使用上述子查询方法，并具有子查询的两个副本。另一种方法是将 FROM tblCompanies 替换为 FROM (SELECT tblCompanies.*, COALESCE(SUM(tblCTerminals.sellValue), 0) AS TotalCTerminalSellValue FROM tblCompanies LEFT JOIN tblCTerminals ON tblCTerminals.Company_ID = tblCompanies.Company_ID GROUP BY tblCompanies.Company_ID) AS tblCompanies，它实质上创建了 tblCompanies 的临时副本，并使用名为 totalCTerminalSellValue 的新字段进行了扩充。因此，在查询的其他位置，您可以将 SUM(tblCTerminals.sellValue) 替换为 tblCompanies.totalCTerminalSellValue。