mysql - 根据条件合并不同行的字段

Question

亚马逊为其市场客户提供 CSV 报告，其中包含有关您销售的每篇文章的信息。每篇文章有四行，如下所示:

+----------------------+------------+-------------------+--------+
|       orderid        | amounttype | amountdescription | amount |
+----------------------+------------+-------------------+--------+
| 305-2406165-0572365  | ItemPrice  | Principal         | 2.98   |
| 305-2406165-0572365  | ItemPrice  | Shipping          | 3.89   |
| 305-2406165-0572365  | ItemFees   | Commission        | -0.45  |
| 305-2406165-0572365  | ItemFees   | ShippingHB        | -0.59  |
+----------------------+------------+-------------------+--------+

如您所见，每篇文章都有四行，两行代表实际售价，两行代表我必须向亚马逊支付的费用。

我使用 MySQL 将此 CSV 文件导入到 SQL 表中。选择包括价格在内的一些数据，如下所示:

SELECT DISTINCT
    report.posteddate AS Date,
    orders.OrderID,
    orders.ExternalOrderID AS AZNr,
    report.amount AS ArtPrice
FROM
    report,
    orders
WHERE
    orders.ExternalOrderID = report.orderid
        AND report.amountdescription = 'Principal'
        AND report.transactiontype = 'Order'
ORDER by Date DESC

为了仅获取商品价格而不包含运费，我进行了选择以仅获取 amountdescription 为“Principal”的行。为了解决我的问题，可以忽略 transactiontype。

我想做的事:

我想提取 amounttype 为“ItemFees”的 amount 的两个字段，将它们添加在一起并将结果显示为单个字段。选择后，一行应如下所示:

+------------+---------+---------------------+----------+-------+
|    Date    | OrderID |        AZNr         | ArtPrice | Fees  |
+------------+---------+---------------------+----------+-------+
| 24.07.2014 |  267720 | 305-2406165-0572365 | 2.98     | -1.04 |
+------------+---------+---------------------+----------+-------+

我尝试对两行运行子查询，并选择 amounttype = "ItemFees"并合并结果，但最终出现错误，指出我的子查询返回多于一行。这是查询:

SELECT DISTINCT
    report.posteddate AS Date,
    orders.OrderID,
    orders.ExternalOrderID AS AZNr,
    report.amount AS ArtPrice,
    (SELECT 
            SUM(report.amount)
        FROM
            report,
            orders
        WHERE
            orders.ExternalOrderID = report.orderid
                AND report.amountdescription = 'Commission') +
    (SELECT 
            SUM(report.amount)
        FROM
            report,
            orders
        WHERE
            orders.ExternalOrderID = report.orderid
                AND report.amountdescription = 'ShippingHB') AS Fees
FROM
    report,
    orders
WHERE
    orders.ExternalOrderID = report.orderid
        AND report.amountdescription = 'Principal'
        AND report.transactiontype = 'Order'
ORDER by Date DESC

有人知道如何在给定条件下对两个不同行的两个值求和(请参阅 WHERE 子句)吗？另外，我需要提取运费，但我认为这是同一个问题。

提前谢谢您。

Answer 1

您可以使用两个查询计算 itemprice 和 itemfees 并将它们连接起来

select a.orderid, a.price, b.fees
from (select orderid, sum(amount) price from report where amounttype='ItemPrice' group by orderid) a
     join (select orderid, sum(amount) fees from report where amounttype='ItemFees' group by orderid) b
     on a.orderid = b.orderid

这假设至少有一行包含 itemprice 和一行包含 itemfees。否则你应该使用外连接。