被JOIN混淆-结果中缺少数据 [英] Confused by JOINs - data missing in result

问题描述

该线程与我的旧线程与条件不同行中的字段组合在一起有关.

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

I adjusted the query I got from this thread to meet some other requirements.

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，但并非报告表中的每个项目在订单表中都有一个条目.

I had to do a LEFT JOIN on the last table to get the DeliveryLand, but not every item from the report-table has one entry in the orders-table.

进行了一些计算之后，我发现此查询不会返回所有条目.应该有25个条目，"typ" =退款"，但我只有20个.某些订单可能包含其他订单(由orderid标识)可能没有的条目.

After I did some calculating I found that this query does not return all entries. There should be 25 entries with "typ" = "Refund", but I only get 20 of them. Some orders might contain entries that other orders (identified by orderid) might not have.

我再次查询以汇总费用，运输费用和仅用于退款的价格:

I did another query to sum up the fees, the shipping costs and the price just for the refunds:

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" ="Order"的相同列进行汇总时，此查询工作得很好.

The first two results, fees and shipping costs, are summed up correctly (I got the original data for comparison), but the last one, the price, isn't correct, its too much. I guess there is some data getting truncated by the LEFT JOIN, but I can't figure out why and where, especially this query works perfectly fine when I sum up the same colums for "transactiontype" = "Order".

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

I don't know why there is some data truncated or missing. Can somebody help me with these confusing JOINs I do in both queries? If you need more information, please ask.

提前谢谢！

查询条件

  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

推荐答案

我将从简化查询开始，它可以在没有JOIN的情况下编写.我还要问为什么您ROUND在SUM之前，这会花费更长的时间并且不够精确.

I'd start by simplifying the query, it can be written without JOINs. I'd also question why you ROUND before the SUM which will take longer and be less precise.

我愿意:

  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')如果为true，则返回1，否则为0，因此，仅对与指定的每个条件匹配的金额求和..如果愿意，可以使用更长的CASE:

As an explanation (amountdescription='ItemFees') returns 1 if true and 0 if not, thus the amounts are only summed that match each condition specified.. you can use the longer CASE if you prefer:

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

您可能会从原始查询中丢失一些数据，因为它依赖于所有链接的子表.如果没有Fees，则不会返回订单的Shipping和Price.

You may be losing some data from the original query as it relies on all the subtables linking up.. if there are no Fees then an order's Shipping and Price would not have been returned.

更新

别忘了您是否在同一orderid上有两种交易类型，它们都将计为同一订单，并且都包含在同一笔总金额中.

Don't forget if you have two transactiontypes on the same orderid, they will all count as the same order and will both be included in the same sum.. to get the transaction types separately use:

  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

这篇关于被JOIN混淆-结果中缺少数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！