SQL连接3个表，一张具有总和列

Question

我敢肯定这很简单，但是我正在努力弄清楚如何使这项工作。我有三张桌子：

1个临时表和2个其他表

下面是桌面化妆：

newartbl(CustID是唯一的( - custid，分配，newbalance

jobbooktbl(LeadID是唯一的( - Leadid，价格

paymentstbl(无唯一列( - custid，金额

custId = leadid = custid

这是我尝试过的，并且无法正常工作：

$sqlnewbal = "SELECT b.custid, j.price, b.assigned, SUM(p.amount) AS sumAmnt
              FROM jobbooktbl j, newartbl b, paymentstbl p
              WHERE j.leadid=b.custid AND b.custid=p.custid";
$qrynewbal = mysqli_query($db,$sqlnewbal);
while ($row = mysqli_fetch_array($qrynewbal)) {
  $custid = $row['custid'];
  $price = $row["price"];
  $paid = $row['sumAmnt'];
  $assigned = $row['assigned'];
  $newPaid = $paid/$assigned;
  $newPrice = $price/$assigned;
  $newBal = $newPrice-$newPaid;
  $usqlUpdate = $db->prepare("UPDATE newartbl SET newBalance=? WHERE custid=?");
  $usqlUpdate->bind_param('di',$newBal,$custid);
  $usqlUpdate->execute();
}

这是输出null。

预期的结果是将来自Paymentstbl的金额与CustID总结，与其他两个TBL相匹配，我想拿走该款项并从JOVBOOKTBL的价格列中减去，然后将新值插入临时表中。/p>

我敢肯定我缺少一些简单的东西，但我似乎无法将手指放在上面。

update

刚刚意识到我没有解释该部门。共享一些Custid，我想找到每个分配的价格和金额。

样本数据：

newartbl-

custid | assigned | newBalance
   1   |    1     |    Null
   2   |    2     |    Null

Jobbooktbl-

leadid | price
   1   | 9400
   2   | 3000

paymentstbl-

custid | amount
   1   |  400

SQL的预期结果应为

newartbl-

custid | assigned | newBalance
   1   |    1     |    9000
   2   |    2     |    1500

实际发生的事情是

custid | assigned | newBalance
   1   |    1     |    Null
   2   |    2     |    Null

这是我想到的解决方案。

$sqlnewbal = "SELECT b.custid, j.price, b.assigned
                FROM jobbooktbl j, newartbl b
                WHERE j.leadid=b.custid";
  $qrynewbal = mysqli_query($db,$sqlnewbal);
  while ($row = mysqli_fetch_array($qrynewbal)) {
    $custid = $row['custid'];
    $price = $row["price"];
    $assigned = $row['assigned'];
    $newBal = $price/$assigned;
    $usqlUpdate = $db->prepare("UPDATE newartbl SET newBalance=? WHERE custid=?");
    $usqlUpdate->bind_param('di',$newBal,$custid);
    $usqlUpdate->execute();
  }
  $sqlnewbal = "SELECT b.custid, b.assigned, b.newBalance, SUM(p.amount) AS sumAmnt
                FROM paymentstbl p, newartbl b
                WHERE p.custid=b.custid";
  $qrynewbal = mysqli_query($db,$sqlnewbal);
  while ($row = mysqli_fetch_array($qrynewbal)) {
    $custid = $row['custid'];
    $paid = $row["sumAmnt"];
    $price = $row["newBalance"];
    $assigned = $row['assigned'];
    $newPaid = $paid/$assigned;
    $newBal = $price-$newPaid;
    $usqlUpdate = $db->prepare("UPDATE newartbl SET newBalance=? WHERE custid=?");
    $usqlUpdate->bind_param('di',$newBal,$custid);
    $usqlUpdate->execute();
  }

Answer 1

考虑没有PHP循环的纯SQL解决方案。MySQL可以在UPDATE语句上运行加入，您可以通过汇总子查询更新。请注意，您的查询已调整以使用明确的JOIN和完整的GROUP BY，它们均为ANSI-92(行业标准(兼容：

UPDATE newartbl n
INNER JOIN
  (SELECT b.custid, j.price, b.assigned, SUM(p.amount) AS sumAmnt
   FROM jobbooktbl j
   INNER JOIN newartbl b
     ON j.leadid = b.custid
   INNER JOIN paymentstbl p
     ON b.custid = p.custid
   GROUP BY b.custid, j.price, b.assigned) sub
ON n.custid = sub.custid
SET n.newBalance = (sub.price / sub.assigned) - (sub.sumAmnt / sub.assigned);