在mysql视图中计算每个产品的运行余额

问题描述

我在 mysql 中有一个视图 (vwbalance)，我用它来检查我们产品在商店中的当前库存余额，它对一种产品运行良好.这是视图

I have a view (vwbalance) in mysql that i use to check the current inventory balance of our product in the store,It has been working very fine with one product. Here is the view

CREATE VIEW vwbalance AS
SELECT
  a.`id`      AS `Trans No`,
  a.`tdate` AS Siku,
  a.`section` AS `Section`,
  `g`.`p_name` AS `Product`,
  a.`cr`      AS `In`,
  a.`dr`      AS `Out`,
  SUM((o.`cr` - o.`dr`)) AS `balance`,
  a.`status`  AS `status`
FROM ((`trn_inventory` a
    LEFT JOIN `mst_product` `g`
      ON ((`g`.`p_id` = a.`p_id`)))
   JOIN `trn_inventory` o
     ON (((a.`tdate` > o.`tdate`)
           OR ((a.`tdate` = o.`tdate`)
               AND (a.`id` >= o.`id`)))))
WHERE (o.`status` = 'APPROVED')
GROUP BY a.`tdate` DESC,a.`id` DESC;

上述视图从两个表 trn_inventory 中获取数据，我们在其中存储所有库存交易(产品进货和产品出货)和 mst_product 表，我们存储产品详细信息.我们创建这个视图的主要原因基本上是为了显示运行余额，因为表 trn_inventory 不存储余额，下面是表定义

The above view gets data from two tables trn_inventory where we store all inventory transactions(products coming in and products going out) and mst_product where we store the product details. Our main reason in creating this view is basically to show the running balance because the table trn_inventory doesnt store the balance, below is the table definition

CREATE TABLE trn_inventory (
  id INT(25) NOT NULL AUTO_INCREMENT,
  tdate DATE NOT NULL,
   p_id INT(25) NOT NULL,
   dr INT(5) DEFAULT '0' COMMENT 'OUT',
  cr INT(5) DEFAULT '0' COMMENT 'IN',
  cost DOUBLE(13,2) NOT NULL DEFAULT '0.00',
  section VARCHAR(95) DEFAULT NULL,
  ref VARCHAR(95) DEFAULT NULL,
  trans_user VARCHAR(35) NOT NULL,
  `status` ENUM('PENDING','APPROVED','DISPATCHED','VOID') NOT NULL DEFAULT 'PENDING',
  approvedby VARCHAR(35) DEFAULT NULL,
  dispatchedby VARCHAR(35) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

这是我运行 SELECT * FROM vwbalance; 时的输出:

Here is the output when I run SELECT * FROM vwbalance;:

Trans  Siku      Section   Product         In   Out  Bal  Status  
-------------------------------------------------------------------
8   2014-02-05  "Store"   "Treated SEEDS"   0   10   68  "APPROVED"
7   2014-02-05  "Store"   "Treated SEEDS"  50    0   78  "APPROVED"
5   2014-02-04  "Store"   "Dry Seeds"      40    0   28  "APPROVED"
3   2014-01-16  "Store"   "Dry Seeds"       0    2  -12  "APPROVED"
4   2014-01-15  "Store"   "Dry Seeds"       0   15  -10  "APPROVED"
2   2014-01-15  "Store"   "Dry Seeds"      10    0    5      "VOID"
1   2014-01-15  "store"   "Dry Seeds"      12    0    5  "APPROVED"
6   2014-01-14  "Store"   "Dry Seeds"       0    7   -7  "APPROVED"

我希望它显示每个产品的余额:

I want it to show the balance per product:

Trans  Siku      Section   Product         In   Out  Bal  Status  
-------------------------------------------------------------------
8   2014-02-05  "Store"   "Treated SEEDS"   0   10   40  "APPROVED"
7   2014-02-05  "Store"   "Treated SEEDS"  50    0   50  "APPROVED"
5   2014-02-04  "Store"   "Dry Seeds"      40    0   28  "APPROVED"
3   2014-01-16  "Store"   "Dry Seeds"       0    2  -12  "APPROVED"
4   2014-01-15  "Store"   "Dry Seeds"       0   15  -10  "APPROVED"
2   2014-01-15  "Store"   "Dry Seeds"      10    0    5      "VOID"
1   2014-01-15  "store"   "Dry Seeds"      12    0    5  "APPROVED"
6   2014-01-14  "Store"   "Dry Seeds"       0    7   -7  "APPROVED"

我修改了分组，

...
...
 WHERE (o.`status` = 'APPROVED')
    GROUP BY a.`tdate` DESC,a.`id` DESC,o.p_id;

但它为下面的第二个产品返回两行是输出

but it was returning two rows for the second product below is the output

Trans  Siku      Section   Product         In   Out  Bal  Status  
-------------------------------------------------------------------
 8  2014-02-05  "Store"   "Treated SEEDS"   0   10   28  "APPROVED"
 8  2014-02-05  "Store"   "Treated SEEDS"   0   10   40  "APPROVED"
 7  2014-02-05  "Store"   "Treated SEEDS"  50    0   28  "APPROVED"
 7  2014-02-05  "Store"   "Treated SEEDS"  50    0   50  "APPROVED"
 5  2014-02-04  "Store"   "Dry Seeds"      40    0   28  "APPROVED"
 3  2014-01-16  "Store"   "Dry Seeds"       0    2  -12  "APPROVED"
 4  2014-01-15  "Store"   "Dry Seeds"       0   15  -10  "APPROVED"
 2  2014-01-15  "Store"   "Dry Seeds"      10    0    5      "VOID"
 1  2014-01-15  "store"   "Dry Seeds"      12    0    5  "APPROVED"
 6  2014-01-14  "Store"   "Dry Seeds"       0    7   -7  "APPROVED"

我哪里出错了?

我创建了一个 SQLFiddle，您可以在其中使用示例数据获取此架构可以测试查询

I have created an SQLFiddle where you can get this schema with sample data where you can test the query

推荐答案

您在创建视图中没有匹配两个表的产品 ID.所以，你需要设置你的条件如下.

You did not match product id of both tables in create view. So, you need to set your condition as below.

 WHERE (o.status = 'APPROVED' and o.p_id = a.p_id)
GROUP BY a.`tdate` DESC,a.`id` DESC,o.p_id;

参见FIDDLE

这篇关于在mysql视图中计算每个产品的运行余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！