sql - 在多个查询中使用 postgres CTE

Question

我可以像这样在单个查询中使用 CTE

with mycte as (...)
  insert into table1 (col1) select col1 from mycte where col1 in
    (select col1 from mycte)

但是如果我想mycte在多个查询中使用呢？我怎样才能使这样的工作？

with mycte as (...)
  insert into table1 (col1) select col1 from mycte where col1 in
    (select col1 from mycte),
  insert into table2 (col1) select col1 from mycte where col1 in
    (select col1 from mycte)

标签： sqlpostgresqlcommon-table-expression

Answer 1

对于多个插入，您可以将它们放入同一个查询中：

with mycte as (...),
     i1 as (
      insert into table1 (col1)
          select col1
          from mycte
          where col1 in (select col1 from mycte)
          returning *
     )
insert into table2 (col1)
    select col1
    from mycte
    where col1 in (select col1 from mycte);