本文介绍了将结果分组到两个表中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有下表。
CREATE TEMPORARY TABLE temp_detail ( purchase_order_detail_id INTEGER, item_id integer, qty numeric(18,2), project_id integer, category_id integer, supplier_id integer, rate numeric(18,2) );我正在使用
SELECT array_agg(purchase_order_detail_id), project_id, category_id, supplier_id FROM temp_detail GROUP BY project_id, category_id, supplier_id现在,我想将project_id,category_id,supplier_id插入主表中,并将item_id,qty,rate插入其明细表中。明细表会将主表ID作为外键。请帮忙。
Now I want to insert project_id, category_id, supplier_id into a master table and item_id,qty,rate into its detail table. Detail table will have the master table id as foreign key. Please help.
推荐答案假定此模式:
create table master ( master_id serial primary key, project_id int, category_id int, supplier_id int ); create table detail ( detail_id int, item_id int, qty numeric(18,2), rate numeric(18,2), master_id int references master (master_id) ); create temporary table temp_detail ( purchase_order_detail_id integer, item_id integer, qty numeric(18,2), project_id integer, category_id integer, supplier_id integer, rate numeric(18,2) );这样做:
with d as ( insert into master (project_id, category_id, supplier_id) select distinct project_id, category_id, supplier_id from temp_detail returning * ) insert into detail (item_id, qty, rate, master_id) select item_id, qty, rate, master_id from temp_detail td inner join d on (td.project_id, td.category_id, td.supplier_id) = (d.project_id, d.category_id, d.supplier_id) ;更多推荐
将结果分组到两个表中
发布评论