将结果分组到两个表中

Question

我有下表。

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

我正在使用ID获取分组结果

  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作为外键。请帮忙。

Answer 1

假设这个模式：

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