我嘗試使用 sql 獲取剩余庫存。我有什么問題。
SELECT (SELECT SUM(quantity) as stock
FROM stock WHERE product_id='4'
GROUP BY product_id
-
SELECT SUM(qty) as sales
FROM order_details
WHERE product_id='4'
)
uj5u.com熱心網友回復：
我認為這就是你所需要的。
如果不正確，請提供表定義和示例資料以及所需的輸出。
SELECT
product_id,
SUM(st.quantity) as stock,
SUM(od.qty) as sales
FROM
stock st
LEFT JOIN order_details od
ON st.product_id = od.product_id
WHERE st.product_id = '4'
GROUP BY product_id;
我們還可以使用以下查詢，它更接近您的原始查詢。
SELECT
'stock' as "item",
SUM(quantity) as "value"
FROM stock WHERE product_id='4'
GROUP BY product_id
UNION ALL
SELECT
'sales',
SUM(qty)
FROM order_details
WHERE product_id='4';
uj5u.com熱心網友回復：
這是我處理平衡和類似事情的核武器，你永遠不會得到任何丟失的資訊，這是我如何做到的，我通過創建 3 個不同的視圖來做到這一點，一個用于訂單，一個用于出售，以及兩者的結合（最后總結）
更新：在實際示例中，您在表中有 ItemID，這是主鍵（不為空，不重復），您將其用作所有 itemID 的參考，同時出售和剩余。對我來說，我用idItem (PK, AI) 和 itemName創建了專案
首先，為股票v_stock創建一個視圖：
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `v_stock` AS
SELECT
`items`.`iditem` AS `ItemID`,
SUM(IFNULL(`stock`.`quantity`, 0)) AS `StockQTY`
FROM
(`items`
LEFT JOIN `stock` ON (`items`.`iditem` = `stock`.`product_id`))
GROUP BY `items`.`iditem`
其次，為訂單v_order創建視圖：
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `v_order` AS
SELECT
`items`.`iditem` AS `ItemID`,
SUM(IFNULL(`order_details`.`qty`, 0)) AS `SoldQTY`
FROM
(`items`
LEFT JOIN `order_details` ON (`items`.`iditem` = `order_details`.`item_id`))
GROUP BY `items`.`iditem`
最后是摘要視圖v_itembalance：
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `v_itembalance` AS
SELECT
`v_stock`.`ItemID` AS `ItemID`,
SUM(`v_stock`.`StockQTY`) AS `inStock_QTY`,
SUM(`v_order`.`SoldQTY`) AS `Sold_QTY`,
SUM(`v_stock`.`StockQTY`) - SUM(`v_order`.`SoldQTY`) AS `Remaining QTY`
FROM
(`v_stock`
JOIN `v_order` ON (`v_stock`.`ItemID` = `v_order`.`ItemID`))
GROUP BY `v_stock`.`ItemID` , `v_order`.`ItemID`
Now, the idea here why I am using INNER not left or right is because or void or null in any of side (order or stock),
how I can get information? well easy:
select * from v_itembalance
Do I need the info for item 4?
select * from v_itembalance where ItemID = '4'
let me know if you still need any help or you don't understand any of this, feel free to hit the upvote :)
select * from v_itembalance;
select * from stock;
select * from order_details;
select * from v_itembalance where ItemID = '4';
Feel free if you have any other needs :P
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/445187.html
標籤：mysql
上一篇：Mysql簡單查詢執行時間過長