亚马逊为其市场客户提供 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”的行。为了解决我的问题,可以忽略交易类型。
我想做的事:
我想提取 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 子句)?另外,我需要提取运费,但我认为这是同一个问题。
先感谢您。