問題描述

我用這個列命名材料的簡單表格:

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查詢和查看中計算運行余額的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！