mysql - MySQL盘点后如何计算

数据表采用以下方式；

Firm | PartID | StockCount |       Date | Type
-----------------------------------------------------------
   1 |     71 |          5 | 2014-02-01 | Incoming Invoice
   1 |     71 |         -1 | 2014-02-09 | Send Invoice
   1 |     71 |         10 | 2014-02-13 | Stocktaking  ( !!! Of the Depot. )
   1 |     71 |         -1 | 2014-02-21 | Send Invoice
   1 |     71 |          5 | 2014-02-28 | Incoming Invoice

该表实际上是股票，是对运动表的虚构描述。在商店中制成的此表中的计数，包括购买和销售发票。这样，从仓库进入仓库后，便会在一个表中收集实际计数的数量。从进行人口普查的那一刻起，应该在规定的数量上计算库存值。问题来了。

如何获得以下结果？

Firm | PartID | StockCount |       Date | Type
-------------------------------------------------------
   1 |     71 |         14 |       NULL | NULL

最佳答案

您似乎想要“盘点”之后的存货总和，我怀疑这在英语中通常更称为“做库存”。

select Firm, PartId, sum(StockCount) as StockCount, NULL as Date, NULL as Type
from table t
where Date >= (select max(Date)
               from table t2
               where t2.Firm = t.Firm and
                     t2.partid = t.partid and
                     t2.type = 'Stocktaking'
              )
group by Firm, Partid;

如果可能没有库存盘点记录，则采用left join方法：

select Firm, PartId, sum(StockCount) as StockCount, NULL as Date, NULL as Type
from table t left join
     (select Firm, PartId, max(Date) as maxDate
      from table t
      where t2.type = 'Stocktaking'
      group by Firm, PartId
     ) as tfp
     on t.Firm = tfp.Firm and t.PartId = tfp.PartId and t.Date >= tfp.MaxDate
group by t.Firm, t.PartId;

关于mysql - MySQL盘点后如何计算，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22743212/