根据条件合并来自不同行的字段 [英] Combine fields from different rows on condition

问题描述

Amazon向其市场客户提供CSV报告，其中包含有关所售商品的信息.每篇文章有四行，如下所示:

Amazon offers their marketplace customers a CSV report which contains information about every article you sold. There are four rows per article, looking like this:

+----------------------+------------+-------------------+--------+
|       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  |
+----------------------+------------+-------------------+--------+

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

As you can see, every article has four rows, two for the actual selling price and two for the fees I have to pay to Amazon.

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

I import this CSV file into a SQL-table using MySQL. Selecting some data including the price looks like this:

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

要仅获取商品价格而不收取运费，我做了一个选择，仅获取金额描述为委托人"的行.为了解决我的问题，可以忽略transactiontype.

To get just the item price without the shipping I do a selection to get only the rows where amountdescription is "Principal". The transactiontype can be ignored in order to solve my problem.

我想做什么:

我想提取数量类型为"ItemFees"的数量的两个字段，将它们加在一起并将结果显示为单个字段.选择之后，一行应如下所示:

I want to extract both fields of amount where amounttype is "ItemFees", add them together and display the result as a single field. After this selection, a row should look like this:

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

我尝试对两行都运行一个子查询，选择的类型为amounttype ="ItemFees"并合并结果，但是最终出现错误，说我的子查询返回了多行.这是查询:

I tried to run a subquery for both rows with a selection to amounttype = "ItemFees" and combine the results, but I ended up in an error saying that my subquery returns more than one row. This is the query:

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条款)?另外，我需要提取运输价值，但是我认为这是同样的问题.

Does anybody have an idea how to sum up two values from two different rows with the given condition (see WHERE-clause)? Also, I need to extract the shipping value, but I think this is the same question.

谢谢.

推荐答案

您可以通过两个查询来计算itemprice和itemfees并将其加入

you can calculate itemprice and itemfees with two queries and join them

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的行.否则，您应该使用外部联接.

this asumes there is at least one row with itemprice and one row with itemfees. otherwise you should use an outer join.

这篇关于根据条件合并来自不同行的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！