SQL将所有数据插入到桥接表中(多对多)

Question

我有两张表，如下所示；

X表；

+---+----------+
| id| value    |
+---+----------+
| 1 | x value1 |
+---+----------+
| 2 | x value2 |
+---+----------+
| 3 | x value3 |
+---+----------+

Y表；

+---+----------+
| id| value    |
+---+----------+
| 1 | y value1 |
+---+----------+
| 2 | y value2 |
+---+----------+
| 3 | y value3 |
+---+----------+

并且我已经创建了一个新的表(x_y表)，它有x和y表的外键；

我想将所有相互关联的数据添加到新表中，如下所示: x_y表

+----+------+------+
| id | x_id | y_id |
+----+------+------+
| 1  | 1    | 1    |
+----+------+------+
| 2  | 1    | 2    |
+----+------+------+
| 3  | 1    | 3    |
+----+------+------+
| 4  | 2    | 1    |
+----+------+------+
| 5  | 2    | 2    |
+----+------+------+
| 6  | 2    | 3    |
+----+------+------+
| 7  | 3    | 1    |
+----+------+------+
| 8  | 3    | 2    |
+----+------+------+
| 9  | 3    | 3    |
+----+------+------+

如何将这样的值添加到postgresql脚本的第三个表中。

Answer 1

这可以通过一个cross join和一个生成id的row_number来完成。

select row_number() over(order by x.id,y.id) as id,x.id as x_id,y.id as y_id
from x
cross join y

Answer 2

据推测，新表是用id定义为序列列的。如果是这样，您可以通过执行以下操作来插入数据：

insert into x_y (x_id, y_id)
    select x.id, y.id
    from x cross join
         y
    order by x.id, y.id;