使用视图计算 mysql 中的运行余额

Question

我的简单表名为ass_material，包含以下列：

mat_id    mat_name  supplier    request    stock_received   date 
1           alloy      test       30         0             feb13
2           alloy      test       30         10            feb14
3           alloy      test       30         20            feb14 

我如何生成它或计算它为：它还将在stock_balance和请求匹配时生成"完成"状态。

  mat_id    mat_name  supplier    request    stock_received  Stock_balance   date    status
    1           alloy      test       30         0                 0        feb13
    2           alloy      test       30         10               10        feb14
    3           alloy      test       30         20               30        feb14   complete

这是我从另一个视图中获取的参考代码：如何将其调整为上面所需的输出？

SELECT m.`mat_id`, m.`mat_name`, m.`request`, m.`stock_receieved`,
       (select sum(stock_received) - sum(stock_received)
        from material m2
        where m2.mat_name = m.mat_name and
              m2.mat_id <= m.mat_id
       ) as Stock_balance,
      m.`date`,
      s.`sup_name`
FROM `material` m
LEFT JOIN `supplier` s on s.sup_id = m.supplier_id
ORDER BY m.`mat_id` ASC;

Answer 1

如果stock_balance只是stock_received之和，则在子查询中使用它：

SELECT m.`mat_id`, m.`mat_name`, m.`request`, m.`stock_receieved`,
       (select sum(stock_received)
        from material m2
        where m2.mat_name = m.mat_name and
              m2.mat_id <= m.mat_id
       ) as Stock_balance,
       (case when (select sum(stock_received)
                   from material m2
                   where m2.mat_name = m.mat_name and
                         m2.mat_id <= m.mat_id
                  ) = request
             then 'Complete'
             else 'Incomplete'
        end) as status,
      m.`date`,
      s.`sup_name`
FROM `material` m LEFT JOIN
     `supplier` s
     on s.sup_id = m.supplier_id
ORDER BY m.`mat_id` ASC;