mysql - 对 JOIN 感到困惑 - 结果中数据丢失

Question

此线程与我的旧线程相关 Combine fields from different rows on condition .

我调整了从该线程获得的查询以满足一些其他要求。

SELECT 
    a.Date,
    a.orderid AS AZNr,
    a.Typ,
    ROUND(a.Fees, 2) AS Fees,
    ROUND(b.Shipping, 2) AS Shipping,
    ROUND(c.Price, 2) AS Price,
    d.DeliveryLand
FROM
    (SELECT 
        posteddate AS Date,
            transactiontype AS Typ,
            orderid,
            SUM(amount) AS Fees
    FROM
        report
    WHERE
        amounttype = 'ItemFees'
    GROUP BY orderid) a
        LEFT JOIN
    (SELECT 
        orderid, SUM(amount) AS Shipping
    FROM
        report
    WHERE
        amountdescription = 'Shipping'
    GROUP BY orderid) b ON a.orderid = b.orderid
        LEFT JOIN
    (SELECT 
        orderid, SUM(amount) AS Price
    FROM
        report
    WHERE
        amountdescription = 'Principal'
    GROUP BY orderid) c ON b.orderid = c.orderid
        LEFT JOIN
    (SELECT 
        DeliveryLand, ExternalOrderId
    FROM
        orders) d ON c.orderid = d.ExternalOrderId
ORDER BY Date DESC

我必须在最后一个表上执行LEFT JOIN才能获取DeliveryLand，但并非报告表中的每个项目在订单表中都有一个条目。

经过一些计算，我发现这个查询并没有返回所有条目。应该有 25 个带有“typ”=“Refund”的条目，但我只得到了其中的 20 个。某些订单可能包含其他订单(由 orderid 标识)可能没有的条目。

我又做了一次查询来汇总费用、运费和退款价格:

SELECT 
    SUM(ROUND(a.Fees, 2)) AS Fees,
    SUM(ROUND(b.Shipping, 2)) AS Shipping,
    SUM(ROUND(c.Price, 2)) AS Price
FROM
    (SELECT 
        orderid, SUM(amount) AS Fees
    FROM
        report
    WHERE
        amounttype = 'ItemFees'
            AND transactiontype = 'Refund'
    GROUP BY orderid) a
        LEFT JOIN
    (SELECT 
        orderid, SUM(amount) AS Shipping
    FROM
        report
    WHERE
        amountdescription = 'Shipping'
            AND transactiontype = 'Refund'
    GROUP BY orderid) b ON a.orderid = b.orderid
        LEFT JOIN
    (SELECT 
        orderid, SUM(amount) AS Price
    FROM
        report
    WHERE
        amountdescription = 'Principal'
            AND transactiontype = 'Refund'
    GROUP BY orderid) c ON b.orderid = c.orderid

前两项结果，费用和运费，总结正确(我得到了原始数据进行比较)，但最后一项，价格，不正确，太多了。我猜想有一些数据被 LEFT JOIN 截断，但我不知道为什么以及在哪里，特别是当我总结“transactiontype”=“的相同列时，这个查询工作得非常好订单”。

我不知道为什么有些数据被截断或丢失。有人可以帮助我解决我在两个查询中所做的这些令人困惑的 JOIN 吗？如果您需要更多信息，请询问。

提前谢谢您!

€编辑过的查询:

  SELECT 
    posteddate AS Date,
    transactiontype AS Typ,
    report.orderid AS AZNr,
    ROUND(SUM((amounttype = 'ItemFees') * amount),
            2) AS Fees,
    ROUND(SUM((amountdescription = 'Shipping') * amount),
            2) AS Shipping,
    ROUND(SUM((amountdescription = 'Principal') * amount),
            2) AS Price,
    orders.DeliveryLand,
    articles.ItemVAT AS VAT
FROM
    report
        LEFT JOIN
    orders ON report.orderid = orders.ExternalOrderID
        LEFT JOIN
    articles ON report.sku = articles.ItemID
GROUP BY report.orderid , transactiontype

Answer 1

我首先会简化查询，它可以在没有 JOIN 的情况下编写。我还想问为什么你在 SUM 之前进行 ROUND ，这会花费更长的时间并且不太精确。

我会这样做:

  SELECT orderid,
         ROUND(SUM((amountdescription='ItemFees')*amount), 2)) AS Fees,
         ROUND(SUM((amountdescription='Shipping')*amount), 2)) AS Shipping,
         ROUND(SUM((amountdescription='Principal')*amount), 2)) AS Price
    FROM report
   WHERE transactiontype='Refund'
GROUP BY orderid

作为解释，(amountdescription='ItemFees') 如果为真则返回 1，如果不为真则返回 0，因此仅对符合指定每个条件的金额进行求和。您可以使用较长的 CASE(如果您愿意):

SUM(CASE WHEN *condition* THEN amount ELSE 0 END) 

您可能会丢失原始查询中的一些数据，因为它依赖于所有链接的子表。如果没有费用，则订单的运费和价格不会被退回。

更新

不要忘记，如果您在同一个 orderid 上有两个交易类型，它们将全部算作同一订单，并且都包含在相同的总和中。要分别获取交易类型，请使用:

  SELECT orderid, transactiontype,
         ROUND(SUM((amountdescription='ItemFees')*amount), 2)) AS Fees,
         ROUND(SUM((amountdescription='Shipping')*amount), 2)) AS Shipping,
         ROUND(SUM((amountdescription='Principal')*amount), 2)) AS Price
    FROM report
GROUP BY orderid, transactiontype