mysql - 计算日期之间的销售利润

Question

我有一个看起来像这样的表:

+--------+---------------------+-------+--------+-----------+
| PartNo | Date                | Inv   | Retail | Wholesale |
+--------+---------------------+-------+--------+-----------+
| 1      | 2018-05-12 00:00:00 | 15    | $100   | $90       |
| 2      | 2018-05-12 00:00:00 | 20    | $200   | $150      |
| 3      | 2018-05-12 00:00:00 | 25    | $300   | $200      |
| 1      | 2018-05-13 00:00:00 | 10    | $95    | $90       |
| 2      | 2018-05-14 00:00:00 | 15    | $200   | $150      |
| 3      | 2018-05-14 00:00:00 | 20    | $300   | $200      |
+--------+---------------------+-------+--------+-----------+

我希望它在 Mysql 查询中看起来像这样:

+--------+------+--------+
| PartNo | Sold | Profit |
+--------+------+--------+
| 1      | 5    | $25    |
| 2      | 5    | $250   |
| 3      | 5    | $500   |
+--------+------+--------+

我需要在计算一个日期范围内总计和利润之间的差值时按 PartNo 分组。

必须通过从日期范围的最后一天(或记录)的零售中减去批发来计算单位利润。

我觉得这应该很容易，但日期范围的差异让我感到困惑，处理日期范围内的记录不完全在输入的日期范围内开始或结束让我迷失了方向。

如有任何帮助，我们将不胜感激。

谢谢。

Answer 1

期初和期末情况可以查，如果查不到期初情况，则假设没有存货。如果没有找到结束情况，则表示该期间没有销售。

例如，从 2018-05-13 到 2018-05-14 结束的时间段:

select  parts.PartNo
,       coalesce(FirstSale.Total, 0) - coalesce(LastSale.Total, FirstSale.Total, 0) as Sold
,       (coalesce(FirstSale.Total, 0) - coalesce(LastSale.Total, FirstSale.Total, 0)) *
           coalesce(LastSale.Retail - LastSale.Wholesale, 0) as Profit
from    (
        select  PartNo
        ,       max(case when Date < '2018-05-13' then Date end) as FirstEntry
        ,       max(case when Date <= '2018-05-14' then Date end) as LastEntry
        from    Sales
        group by
                PartNo
        ) parts
left join    
        Sales FirstSale
on      FirstSale.PartNo = parts.PartNo
        and FirstSale.Date = parts.FirstEntry
left join    
        Sales LastSale
on      LastSale.PartNo = parts.PartNo
        and LastSale.Date = parts.LastEntry

Example at SQL Fiddle.