将结果分组到两个表中

本文介绍了将结果分组到两个表中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有下表。

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) ;