mysql left join 重复我的表行 - 为什么？

Question

我有 2 个表:

• Table1 => product_id, product_quantity => 这里有 25 行。
• Table2 => product_hashid, order_quantity => 这里我有 1 个query 行。

我构建了这个 mysql 查询:

SELECT SUM(table1.product_quantity) - SUM(order_quantity) AS instocks
  FROM table1  -- table.1 in original
  LEFT JOIN table2 ON table2.product_hashid = table1.product_id
 WHERE table1.product_id = '$thisid'

此查询将表 2 的行与表 1 重复。这个查询有什么错误吗？

首先，我想对 table1 中的所有 product_quantity 求和，其中 product_id = '$this' 并对所有 order_quantity 求和table2 中的 code>，其中 product_hashid = '$this' 并使 (a - b) 显示最终结果。

Answer 1

您对想要做的事情的概述很好，但不是您实现的。

• I want to sum all product_quantity from table1 where product_id = '$this' and sum all order_quantity in table2 where product_hashid = '$this' and make (a - b) to display a final result.

一次构建它。

SELECT SUM(product_quantity) FROM Table1 WHERE Product_ID = '$this';

SELECT SUM(order_quantity) FROM Table2 WHERE Product_HashID = '$this';

SELECT (SELECT SUM(product_quantity) FROM Table1 WHERE Product_ID     = '$this') -
       (SELECT SUM(order_quantity)   FROM Table2 WHERE Product_HashID = '$this')
  FROM Dual;

您可能会注意到代码对齐强​​调了产品 ID 列的列命名不一致。

---

在更一般的情况下:

SELECT Product_ID, SUM(product_quantity) AS Product_Quantity
  FROM Table1
 GROUP BY Product_ID;

SELECT Product_HashID AS Product_ID, SUM(order_quantity) AS Order_Quantity
  FROM Table2
 GROUP BY Product_HashID;

SELECT p.Product_ID, p.Product_Quantity - o.Order_Quantity AS SurplusOnHand
  FROM (SELECT Product_ID, SUM(product_quantity) AS Product_Quantity
          FROM Table1
         GROUP BY Product_ID) AS P
  JOIN (SELECT Product_HashID AS Product_ID, SUM(order_quantity) AS Order_Quantity
          FROM Table2
         GROUP BY Product_HashID) AS O
    ON O.Product_ID = P.Product_ID;

---

有时您需要使用 LEFT OUTER JOIN；大多数情况下，你没有。假设您不会编写 SQL，直到您确定自己这样做了。

鉴于数据基数(行数)，您可能需要在此处执行 LOJ。表1中列出的那些产品，表2中没有列出的产品，您的订单数量需要制造为零。

SELECT    (SELECT SUM(product_quantity) FROM Table1 WHERE Product_ID     = '$this') -
       NVL(SELECT SUM(order_quantity)   FROM Table2 WHERE Product_HashID = '$this'), 0)
  FROM Dual;


SELECT p.Product_ID, p.Product_Quantity - NVL(o.Order_Quantity, 0) AS SurplusOnHand
  FROM (SELECT Product_ID, SUM(product_quantity) AS Product_Quantity
          FROM Table1
         GROUP BY Product_ID) AS P
  LEFT OUTER JOIN
       (SELECT Product_HashID AS Product_ID, SUM(order_quantity) AS Order_Quantity
          FROM Table2
         GROUP BY Product_HashID) AS O
    ON O.Product_ID = P.Product_ID;