在mysql select查询和查看中计算运行余额 [英] Calculate running balance in mysql select query and view

问题描述

我用这个列命名材料的简单表格:

My simple table named material with this columns:

mat_id       mat_name      stock_in       stock_released    date
1             alloy          30                0            feb13
2             steel          15                0            feb13
3             alloy          0                 3            feb14

我如何生成或计算它:

mat_id       mat_name      stock_in       stock_released    Balance         date
1             alloy          30                0               30           feb13
2             steel          15                0               15           feb13
3             alloy          0                 3               15           feb14

这是我现有的代码，但天平有问题，因为它总计了所有内容，不考虑材料名称:

here is my existing code but there is something wrong with the balance because it totals everything disregarding the material name:

SELECT 
    `material`.`mat_id`,
    `material`.`mat_name`,
    `material`.`stock_in`,
    `material`.`stock_released`,

    @Balance := @Balance + `material`.`stock_in` - `material`.`stock_released` AS `Balance`,
    `material`.`date`
FROM `material`, (SELECT @Balance := 0) AS variableInit
WHERE mat_name = mat_name
ORDER BY `material`.`mat_id` ASC

它如何创建视图?

推荐答案

有了这个数据，得到运行余额的查询是:

With this data, the query that gets the running balance is:

SELECT m.`mat_id`, m.`mat_name`, m.`stock_in`, m.`stock_released`,
       (select sum(stock_in) - sum(stock_released)
        from material m2
        where m2.mat_name = m.mat_name and
              m2.mat_id <= m.mat_id
       ) as balance,
      m.`date`
FROM `material` m
ORDER BY m.`mat_id` ASC;

您可以将其放入视图中.

This you can put into a view.

这篇关于在mysql select查询和查看中计算运行余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！