mysql - 总结 MySQL 中不同行的字段 - 小但重要的错误

Question

该主题与我在该主题中提出的查询相关

Combine fields from different rows on condition

及其延续

Confused by JOINs - data missing in result

如果行中的金额类型是 ItemFees、Promotion 或 Shipping(描述)，则查询将对 amount 中的所有值进行求和。描述的查询(有关详细说明，请参阅链接的文章):

SELECT
    posteddate AS Date,
    transactiontype AS Type,
    report.orderid AS OrderID,
    ROUND(SUM((amounttype = 'ItemFees') * amount),
        2) AS Fees,
    ROUND(SUM((amounttype = 'Promotion') * amount),
        2) AS Promo,
ROUND(SUM((amountdescription = 'Shipping') * amount),
        2) AS Shipping,
    ROUND(SUM((amountdescription = 'Principal' AND amounttype = 'ItemPrice') *amount),
            2) AS Price,
    orders.DeliveryLand AS Country,
    items.ItemVAT AS VAT
FROM
    report
        LEFT JOIN
    ordersON report.orderid = orders.ExternalOrderID
        LEFT JOIN
    items ON report.sku = items.ItemID
WHERE DeliveryLand = 1
GROUP BY report.orderid , transactiontype
ORDER BY Date DESC, OrderID ASC;

在大多数情况下，此查询按预期工作。但让我们以这些条目为例:

+-----------------+---------------------+------------+-------------------+--------+
| transactiontype | orderid             | amounttype | amountdescription | amount |
+-----------------+---------------------+------------+-------------------+--------+
| Order           | 305-2406165-0572365 | ItemPrice  | Principal         | 23.24  |
| Order           | 305-2406165-0572365 | ItemPrice  | Shipping          | 3.69   |
| Order           | 305-2406165-0572365 | ItemFees   | Commission        | -3.49  |
| Order           | 305-2406165-0572365 | ItemFees   | ShippingHB        | -0.55  |
| Refund          | 305-2406165-0572365 | ItemPrice  | Principal         | -23.24 |
| Refund          | 305-2406165-0572365 | ItemPrice  | Shipping          | -3.69  |
| Refund          | 305-2406165-0572365 | ItemFees   | Commission        | 3.49   |
| Refund          | 305-2406165-0572365 | ItemFees   | ShippingHB        | 0.55   |
+-----------------+---------------------+------------+-------------------+--------+

如您所见，客户购买了一篇文章，但要求退款。不幸的是，这两个操作都由相同的 orderid 标识，但它们需要分开(我需要单独列出订单和退款)。因此，查询完全按照要求执行，并给出以下输出:

+------------+--------+---------------------+-------+-------+----------+
| date       | type   | orderid             | fees  | promo | shipping | price | country | VAT
+------------+--------+---------------------+-------+-------+--------+--------+---+---+
| 1419375600 | Refund | 305-2406165-0572365 | 8,08  |     0 | -7,38 | -46,48 | 1 | 0 |
+------------+--------+---------------------+-------+-------+-------+--------+---+---+
| 1419375600 | Order  | 305-2406165-0572365 | -8,08 |     0 | 7,38  | 46,48  | 1 | 0 |
+------------+--------+---------------------+-------+-------+-------+--------+---+---+

现在，如果您进行数学计算，您会发现所有值都乘以二。我不明白为什么查询会这样做，以及如何解决这个问题。我需要将所有操作分开，首先由 orderid 标识，然后通过 transactiontype 进行区分。对此有什么想法吗？

正确的是:

+------------+--------+---------------------+-------+-------+----------+
| date       | type   | orderid             | fees  | promo | shipping | price | country | VAT
+------------+--------+---------------------+-------+-------+--------+--------+---+---+
| 1419375600 | Refund | 305-2406165-0572365 | 4,04  |     0 | -3,69 | -23,24 | 1 | 0 |
+------------+--------+---------------------+-------+-------+-------+--------+---+---+
| 1419375600 | Order  | 305-2406165-0572365 | -4,04 |     0 | 3,69  | 23,24  | 1 | 0 |
+------------+--------+---------------------+-------+-------+-------+--------+---+---+

然后，在我写这个问题时发生的另一件事:

还有另一份订单(不退款)包含与上述订单完全相同的值。除非它有不同的 orderid，否则它仍然会列出错误的 double 值!对此感到困惑，希望有人知道答案。

我希望我提供了有关该问题的足够信息。如果没有，请随时索取您需要的任何信息。

Answer 1

首先执行这一点:

SELECT x.transactiontype 
     , x.orderid 
     , ROUND(SUM((amounttype = 'ItemFees') * amount),2) Fees
     , ROUND(SUM((amounttype = 'Promotion') * amount),2) Promo
     , ROUND(SUM((amountdescription = 'Shipping') * amount),2) Shipping
     , ROUND(SUM((amountdescription = 'Principal' AND amounttype = 'ItemPrice') * amount),2) Price
  FROM report x
 GROUP 
    BY transactiontype
     , orderid

然后将其作为子查询连接到查询的其余部分