在mysql中计算运行期末库存

dced5bon 于 2023-01-01 发布在 Mysql

关注(0)|答案(1)|浏览(112)

我需要在库存表中插入新的数量，插入时关闭库存应该重置。

INSERT INTO `a1` (`id`, `trs_date`, `product_id`, `qty_in`, `qty_out`, `qty_bal`) VALUES
(1, '2022-01-01', 1, 10, 0, 10),
(2, '2022-01-01', 2, 20, 0, 20),
(3, '2022-01-02', 1, 5, 0, 15),
(4, '2022-01-02', 2, 3, 0, 23),
(5, '2022-01-03', 1, 0, 8, 7);

新数据将取自表a2，作为

INSERT INTO `a2` (`id`, `trs_date`, `product_id`, `qty_in`, `qty_out`) VALUES
(1, '2022-01-04', 1, 50, 0),
(2, '2022-01-04', 2, 60, 0),
(3, '2022-01-05', 1, 0, 10);

我需要将数据从a2添加到a1。插入时，qty_bal应更新为
qty_bal = previous_qty_bal + quantity_in - quantity_out为每个产品。我的代码如下

INSERT INTO 
    a1 (trs_date, product_id, qty_in, qty_out, qty_bal) 
    ( WITH cte_tbl AS ( 
            (select trs_date, product_id, qty_in, qty_out, qty_bal from ( SELECT *, row_number() over (partition by `product_id` order by `trs_date` desc) rn FROM a1 ) existing_latest_data where rn = 1) 
            
            UNION ALL 
            
            (SELECT trs_date, product_id, qty_in, qty_out, 0 AS qty_bal FROM a2) 
        ) 
        SELECT 
            trs_date, product_id, qty_in, qty_out, 
            lag(qty_bal) over (PARTITION BY product_id ORDER BY trs_date) + qty_in- qty_out as qty_bal 
        FROM cte_tbl ORDER BY trs_date 
    )

结果表a1为

我期望的结果应该是

那我该改什么呢。

mysql

来源：https://stackoverflow.com/questions/74942995/calculate-running-clossing-stock-in-mysql

1条答案

按热度按时间

slmsl1lt1#

1.获取每个产品的最后一个qty_bal（注意order by trs_date desc, id desc，以防一天内出现多行）
1.在a2中接收的并集（添加了skip_flag）
1.每行计算新的qty_bal（到order by trs_date, id以按正确顺序获取数据）

insert into a1 (trs_date, product_id, qty_in, qty_out, qty_bal)
with cte_balance as (
select id, trs_date, product_id, qty_in, qty_out, qty_bal,
       row_number() over (partition by product_id order by trs_date desc, id desc) as rn
  from a1),
cte_receive as (
select id, trs_date, product_id, 0 as qty_in, 0 as qty_out, qty_bal, rn as skip_flag from cte_balance where rn = 1
 union all
select id, trs_date, product_id, qty_in, qty_out, qty_in-qty_out, 0 from a2),
cte_new_balance as (
select id, trs_date, product_id, qty_in, qty_out, qty_bal,
       sum(qty_bal) over (partition by product_id order by trs_date, id) as new_bal,
       skip_flag
  from cte_receive)
select trs_date, product_id, qty_in, qty_out, new_bal as qty_bal
  from cte_new_balance
 where skip_flag = 0;

赞(0）回复(0）举报 2023-01-01

我来回答

在mysql中计算运行期末库存

1条答案

相关问题

热门标签

最新问答