将结果分组到两个表中 [英] Grouped result insert into two tables

问题描述

我有下表。

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

这篇关于将结果分组到两个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！