問題描述
我在 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模板網！