需要MySQL查询FIFO。

Question

需要MySQL查询FIFO。

5

请帮助我如何创建基于 FIFO 方法的 MySQL 查询报表。

表 persediaan_source

id	id_barang	jumlah	harga	tanggal	id_jenis_transaksi
89	26	12	1050000	2022-07-15 05:55:23	1
90	26	8	0	2022-07-15 05:55:52	2
91	26	16	1100000	2022-07-15 05:56:22	1
95	26	10	0	2022-07-15 05:59:09	2

id_jenis_transaksi = 1 is Buy
id_jenis_transaksi = 2 is Use

我需要像下面这样的报告

id	日期	备注	买入数量	买入单价	买入总价	使用数量	使用数量明细	使用单价	使用总价	持仓数量	持仓数量明细	持仓单价	持仓总价
1	2022年7月15日 05点55分23秒	买入	12	1,050,000	12,600,000	0	0	0	0	12	12	1,050,000	12,600,000
2	2022年7月15日 05点55分52秒	使用	0	0	0	8	8	1,050,000	8,400,000	4	4	1,050,000	4,200,000
3	2022年7月15日 05点56分22秒	买入	16	1,100,000	17,600,000	0	0	0	0	20	4	1,050,000	4,200,000
4	2022年7月15日 05点56分22秒	买入	0	0	0	0	0	0	0	0	16	1,100,000	17,600,000
5	2022年7月15日第三行必须在bal_qty_detail中进行拆分，因为存在不同的价格并且有残余数量来自先前的价格，同样在第五行必须在use_qty_detail中进行拆分。 CREATE TABLE `persediaan_source` ( `id` int(11) NOT NULL, `id_barang` int(11) NOT NULL, `jumlah` double NOT NULL, `harga` double NOT NULL, `tanggal` datetime NOT NULL, `id_jenis_transaksi` tinyint(4) NOT NULL COMMENT 'id = 1 -> buy, id = 2 -> use' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `persediaan_source` (`id`, `id_barang`, `jumlah`, `harga`, `tanggal`, `id_jenis_transaksi`) VALUES (89, 26, 12, 1050000, '2022-07-15 05:55:23', 1), (90, 26, 8, 0, '2022-07-15 05:55:52', 2), (91, 26, 16, 1100000, '2022-07-15 05:56:22', 1), (95, 26, 10, 0, '2022-07-15 05:59:09', 2);

- Dodo Ananto

也许这个链接可以帮助。 - Asgar

在我寻求帮助之前，我已经尝试过那个链接了，但是链接并不符合我的需求，在链接中没有显示价格。 - Dodo Ananto

第4行和第6行的逻辑是什么？为什么第4行和第6行的列都是0？使用相关列=0的行来进行购买是可以理解的，但在这种情况下几乎所有列都是0！ - Pankaj

1

我认为这份报告在逻辑上存在问题。生成报告的规则不太清晰。 - Liki Crus

我建议您在应用程序代码中完成。当迭代行时，SQL非常混乱；它更喜欢一次处理所有行。 - Rick James

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Nandan Rana · Accepted Answer

您好，使用以下过程，您可以实现所述的报告格式。但是，为了处理FIFO的细节，您应该避免在MySQL中创建这些逻辑。

SELECT @bal_qty:=0,@old_qty:=0,@old_qty_price:=0,@new_qty:=0,@new_qty_price:=0;
CREATE TEMPORARY TABLE test1
SELECT id, tanggal as date, 
@remarks:=if(id_jenis_transaksi=1,'Buy','Use') as remarks, 
@buy_qty:=if(id_jenis_transaksi=1,jumlah,0) as buy_qty, 
@buy_price:=if(id_jenis_transaksi=1,harga,0) as buy_price,  
@buy_total:=(@buy_qty * @buy_price) as buy_total, 
@use_qty:=if(id_jenis_transaksi=2,jumlah,0) as use_qty, 
@use_qty_detail:=if(@remarks='Use',if(@old_qty>0,@old_qty, @use_qty),0) as use_qty_detail, 
@use_price:=@old_qty_price as use_price, 
@use_total:=(@use_qty * @use_price) as use_total, 
@bal_qty:=if(@remarks='Buy',@bal_qty + @buy_qty, if(@bal_qty>@use_qty,@bal_qty - @use_qty, @bal_qty)) as bal_qty, 
@old_qty:=if(@old_qty > 0, @old_qty, if(@remarks='Use',@bal_qty, @old_qty)) as old_qty,
@old_qty_price:=if(@old_qty_price > 0, @old_qty_price, @buy_price) as old_qty_price,  
@new_qty:=if(@old_qty>0,@buy_qty, if(@new_qty>0, @new_qty,0)) as new_qty,
@new_qty_price:=if(@old_qty_price > 0 and @buy_price > 0, @buy_price, if(@new_qty_price>0,@new_qty_price,0)) as new_qty_price,
@bal_qty_detail:=if(@old_qty > 0, @old_qty, @buy_qty) as bal_qty_detail, 
@bal_price:=@old_qty_price as bal_price,
@bal_total:=(@bal_qty_detail* @old_qty_price) as bal_total,
@split_flag:=if(@bal_qty != @buy_qty and @bal_qty != @old_qty,1,0) as split_flag 
FROM persediaan_source;

CREATE TEMPORARY TABLE test2 select * from test1 where split_flag=1

SELECT @cnt:=0;

SELECT (@cnt:=@cnt + 1) as id, date, remarks, buy_qty, buy_price, buy_total, use_qty, use_qty_detail, use_price, use_total, bal_qty, bal_qty_detail, bal_price, bal_total 
FROM (
     (SELECT * FROM test1)
UNION ALL
(SELECT id, date, remarks, 0 as buy_qty, 0 as buy_price, 0 as buy_total, 0 as use_qty, 
@used_qty:=(use_qty - use_qty_detail) as use_qty_detail, 
if(remarks='Use',@new_qty_price,0) as use_price, 
(@used_qty * new_qty_price)  as use_total, 0 as bal_qty, 0 as old_qty, 
0 as old_qty_price, 0 as new_qty,0 as new_qty_price, 
@bal_qty_detail:=if(remarks='Buy', buy_qty,  bal_qty) as bal_qty_detail, 
new_qty_price as bal_price, 
(@bal_qty_detail * new_qty_price) as bal_total, split_flag 
 FROM test2)
) as t order by 2