如何用MySql计算初始库存

Question

我试图找出一种方法，用周数来计算初始库存。我刚刚计算出了在第10周到第20周之间的“进货”和“缺货”这一要求。

有人能给我一个完整的请求来计算最终库存和初始库存吗？

SELECT 
      `week` ,
       SUM(case
         when  `week` between 10 AND 20 and `etat` =1
         then 1
         else 0
       end) AS inn
       ,
       SUM(case
         when `week` between 10 AND 20   and `etat` =2
         then 1
         else 0
       end)  AS out

       FROM fait_stock f
       where 1=1
       group by `week` 
       having `week` between 10 AND 20 

编辑

我想计算前几周的股票(初始股票，意思是weeks<10)，例如，第10周，我有5周。第11周，应该是5+(第11周的股票)。第12周应该是5+(第11周的股票)+(第12周的股票)与其他周相同。

Answer 1

首先，您可以编写更短的查询：

SELECT 
    `week` ,
    SUM(IF(`etat`=1, 1, 0)) AS inn,
    SUM(IF(`etat`=2, 1, 0)) AS `out`
FROM fait_stock
WHERE `week` BETWEEN 10 AND 20
GROUP BY `week`

如果您现在加入它本身(通过一个小的修改，如果您希望周数小于10)，您可以计算小于或等于给定周的所有周的和：

SELECT f1.`week`, SUM(f2.inn) AS inn, SUM(f2.`out`) AS `out`
FROM (
    SELECT 
        `week` ,
        SUM(IF(`etat`=1, 1, 0)) AS inn,
        SUM(IF(`etat`=2, 1, 0)) AS `out`
    FROM fait_stock
    WHERE `week` BETWEEN 10 AND 20
    GROUP BY `week`
) AS f1
JOIN (
    SELECT 
        `week` ,
        SUM(IF(`etat`=1, 1, 0)) AS inn,
        SUM(IF(`etat`=2, 1, 0)) AS `out`
    FROM fait_stock
    WHERE `week` BETWEEN 1 AND 20
    GROUP BY `week`
) AS f2 ON f2.`week` <= f1.`week`
GROUP BY f1.`week`