sql - 将SQL重写为JOINS而不是子查询

Question

我有以下查询。它使用三个子查询，并且经常执行，因此我想将查询重写为JOIN。尽管我的值不正确，但是我尝试这样做，有人知道我如何成功重写查询吗？

带有子查询的查询

SELECT  PRODUCT.ID 
FROM    PRODUCT WHERE (
            COALESCE((SELECT SUM(AMOUNT) FROM CORRECTION WHERE CORRECTION.PRODUCT_ID = PRODUCT.ID),0)
            - COALESCE((SELECT SUM(AMOUNT) FROM ORDERROW WHERE ORDERROW.PRODUCTID = PRODUCT.ID),0)
            + COALESCE((SELECT SUM(AMOUNTOFPACKAGES * AMOUNTPERPACKAGE) FROM DELIVERYROW WHERE DELIVERYROW.PRODUCTID = PRODUCT.ID),0)
            ) > 0

连接的错误查询

SELECT  PRODUCT.ID
FROM    PRODUCT
LEFT JOIN CORRECTION  ON CORRECTION.PRODUCT_ID = PRODUCT.ID
LEFT JOIN ORDERROW    ON ORDERROW.PRODUCTID = PRODUCT.ID
LEFT JOIN DELIVERYROW ON DELIVERYROW.PRODUCTID = PRODUCT.ID
GROUP BY PRODUCT.ID
HAVING  (COALESCE(SUM(CORRECTION.AMOUNT),0)
        - COALESCE(SUM(ORDERROW.AMOUNT),0)
        + COALESCE(SUM(DELIVERYROW.AMOUNTOFPACKAGES * DELIVERYROW.AMOUNTPERPACKAGE),0) 
        ) > 0

Answer 1

重写的问题在于，每个联接表中给定产品的行可能很多，并且总结果集将为Cartesian product；它的行数等于每个联接表中匹配行的数量相乘。

您可以确保每个联接表每个产品最多具有一行:

SELECT  PRODUCT.ID
FROM    PRODUCT
LEFT JOIN (SELECT PRODUCTID, SUM(AMOUNT) AS AMOUNT 
           FROM CORRECTION GROUP BY PRODUCTID) AS C
  ON C.PRODUCT_ID = PRODUCT.ID
LEFT JOIN (SELECT PRODUCTID, SUM(AMOUNT) AS AMOUNT 
           FROM ORDERROW GROUP BY PRODUCTID) AS O
  ON O.PRODUCTID = PRODUCT.ID
LEFT JOIN (SELECT PRODUCTID, SUM(AMOUNTOFPACKAGES * AMOUNTPERPACKAGE) AS AMOUNT 
           FROM DELIVERYROW GROUP BY PRODUCTID) AS D
  ON D.PRODUCTID = PRODUCT.ID
WHERE COALESCE(C.AMOUNT,0)
    - COALESCE(O.AMOUNT,0)
    + COALESCE(D.AMOUNT,0) > 0;

从技术上讲，该解决方案仍使用子查询，但是派生表子查询通常比您使用的相关子查询便宜。与往常一样，仅通过测试就可以确定它将如何影响您的数据。