MySQL 8.0的基于CALC FIFO的库存评估

发布于 2025-01-23 23:53:03 字数 1916 浏览 5 评论 0原文

所有人的美好一天。我花了三天多的时间进行搜索和测试，但没有任何帮助。让我们假设我有很多方法可以将产品项目输入存储，所以我创建了“视图”以向我展示“这样的产品交易”，例如：

product_id	storage_id	type	unit_cost nouts_cost	82	数量
1	in	1	10	2022-04-25	17 ：04：13
82	1	in	2	10	2022-04-25 17:04:51
82	1	in	3	10	2022-04-25 17:07:13
82	16	in	2	10	2022-04-25 17:10:51
82	16	in	3	10	2022-04-25 17:12:13

我还通过订单（订单和订单项目表查看）

product_id	storage_id	量	日期
82	1	15	2022-04-25 17:06:06:
27 82	1	7	2022-- 04-25 17:08:24
82	2	5	2022-04-25 17:20:13

因此，从这些表和视图中，是否有任何询问我的库存评估（未出售商品成本），并根据FIFO和lifo 该查询应显示如下（FIFO）：

product_id	storage_id	剩余_quantity	total_cost
82	1	8	24
82	16	15	40

（lifo）：

product_id	storage_id	re re re re re re re re retaning_quantity	total_cost
82	1	8	14
82 16	15	15 35	35

原文

nice day for all.
I spent more than three days of searching and testing but nothing helped me.
let us assume I have many way to enter items of product to storages, So I created 'view' to show me the product transactions "in" like this:

product_id	storage_id	type	unit_cost	quantity	date
82	1	in	1	10	2022-04-25 17:04:13
82	1	in	2	10	2022-04-25 17:04:51
82	1	in	3	10	2022-04-25 17:07:13
82	16	in	2	10	2022-04-25 17:10:51
82	16	in	3	10	2022-04-25 17:12:13

also I sold items by orders (order and order items tables view)

product_id	storage_id	quantity	date
82	1	15	2022-04-25 17:06:27
82	1	7	2022-04-25 17:08:24
82	2	5	2022-04-25 17:20:13

so from these tables and views, is there any query to calc my inventory valuation (cost of goods didn't sold) based on FIFO and LIFO
that query should show data as follow(FIFO):