本文共 1776 字，大约阅读时间需要 5 分钟。
1. 使用临时表暂存数据，然后拼接得到的所有结果(postgresql)
drop table IF EXISTS temp_stock_beginning;
drop table IF EXISTS temp_stock_move_in;
drop table IF EXISTS temp_stock_move_out;
create temp table temp_stock_beginning(product_id int, location_id int,qty int,res_company_id int);
create temp table temp_stock_move_in(product_id int, location_id int,qty int,res_company_id int);
create temp table temp_stock_move_out(product_id int, location_id int,qty int,res_company_id int);
insert into temp_stock_beginning(product_id,location_id,qty,res_company_id int)
select product_id,stock_location_id as location_id,qty,res_company_id
from company_stock_remain
where data_date='{}';
insert into temp_stock_move_in(product_id,location_id,qty,res_company_id)
select product_id,b.origin_id as location_id,-1*qty,res_company_id
from company_stock_move_detail a
inner join stock_location b on a.from_stock_location_id=b.id
where b.usage='internal' and a.data_date='{}';
insert into temp_stock_move_out(product_id,location_id,qty,res_company_id)
select product_id,b.origin_id as location_id,qty ,res_company_id
from company_stock_move_detail a
inner join stock_location b on a.to_stock_location_id=b.id
where b.usage='internal' and a.data_date='{}';
insert into company_stock_remain(product_id,stock_location_id,qty,data_date,res_company_id)
select product_id,location_id,sum(qty),'{}',res_company_id from
(select product_id,location_id,qty,res_company_id from temp_stock_beginning
union all
select product_id,location_id,qty,res_company_id from temp_stock_move_in
union all
select product_id,location_id,qty,res_company_id from temp_stock_move_out
) as a
where qty != 0
group by product_id,location_id
标签：product,临时,company,qty,location,mysql,暂存,id,stock
来源： https://www.cnblogs.com/JackShi/p/14078357.html
转载地址：https://blog.csdn.net/weixin_33245447/article/details/114817540 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！
发表评论
最新留言
关于作者
