sql - Postgres，执行多个查询，保持 1 :1 ratio

Question

我必须一次运行 3 个查询。因此，在第一个语句中，我得到了必须为接下来的两个语句运行它的次数。问题是最后一个命令执行的次数比需要的次数多。
我写的查询是:

WITH rows_1 AS (
    SELECT t1.name, t1.value
    FROM table1 AS t1
    INNER JOIN aux_table2 AS ps
    ON ps.id = t1.id AND ps.value='pepe'
    )
, rows_2 AS  (
    INSERT INTO table2 (value1, value2)
    SELECT 'value1', 'value2' FROM rows_1 as sr
    RETURNING id
)
INSERT INTO table3
    (value1, value2, value3)
SELECT sr.value, st.id, sr.name,
FROM rows_1 AS sr, rows_2 AS st
;

问题是第一个查询给出了我们拥有的对象数量(N)，后面两个查询都要分别做N次，所以两次插入的比例是1:1，我没能实现它，最后一个当前执行了 NxN 次。

Answer 1

目前还不清楚这些表格是如何排列的。进入 rows_2 的行除了在此查询中不可见的默认值之外，所有内容都相同。

也就是说，您可以为每个行集添加一个序列号并将其用于 JOIN :

WITH rows_1 AS (
    SELECT t1.name, t1.value, ROW_NUMBER() OVER () as seqnum
    FROM table1 t1 JOIN
        aux_table2 ps
        ON ps.id = t1.id AND ps.value='pepe'
    ),
    rows_2 AS  (
      INSERT INTO table2 (value1, value2)
          SELECT 'value1', 'value2'
          FROM rows_1 sr
          RETURNING id
    )
INSERT INTO table3 (value1, value2, value3)
    SELECT sr.value, st.id, sr.name,
    FROM rows_1 sr JOIN
         (SELECT ROW_NUMBER() OVER (ORDER BY id) as seqnum
          FROM rows_2 st
         ) st
         ON st.seqnum = sr.seqnum;