如何复制属于一个id的多个行，并将其添加到postgreSQL中的所有其他id

Question

因此，我有具有唯一I和代码的表总线：

| bus_id        | code        |
|:--------------|------------:|
| 1             |        A    |   
| 2             |        A    |   
| 3             |        A    | 
| 4             |        B    |  
| 5             |        B    | 

现在，对于bus_id，我将这些图像及其in放在另一个表images中，如下所示：

| image_id(not unique)| image_link | bus_id   | 
|:--------------------|-----------:|---------:|
| 1                   |     aaa.com|         1|   
| 2                   |     aab.com|         1|      
| 3                   |     aac.com|         4|    
| 4                   |     aad.com|         4|    

我如何扩展这个映像表，以便对于表bus中具有相同代码的每个bus_id，将在表映像中为每个总线代码中的所有总线创建重复的行。

因此，我在上面的例子中寻找的结果是：

| image_id(not unique)| image_link | bus_id   | 
|:--------------------|-----------:|---------:|
| 1                   |     aaa.com|         1|   
| 2                   |     aab.com|         1| 
| 1                   |     aaa.com|         2|   
| 2                   |     aab.com|         2|      
| 1                   |     aaa.com|         3|   
| 2                   |     aab.com|         3|

(如您所见，bus_id 1:3都有来自表总线的代码A)

| 3                   |     aac.com|         4|    
| 4                   |     aad.com|         4| 
| 3                   |     aac.com|         5|    
| 4                   |     aad.com|         5|

(如您所见，bus_id 4:5都有来自表总线的代码B)

Answer 1

您可以通过生成所有图像，然后过滤掉存在的图像来获得需要添加的图像。

我认为这就是你想要的：

select b.bus_id, b.code, ii.image_id
from busses b join
     (select distinct i.image_id, b.code
      from images i join
           busses b
           on i.bus_id = b.bus_id
     ) ii
     on ii.code = b.code left join
     images i
     on i.bus_id = b.bus_id and i.image_id = ii.image_id
where i.image_id is null;

Answer 2

在代码上将总线连接到它们自己，以便对于代码的每个总线和相同代码的每个总线都存在一对。然后将图像连接到成对的第二条总线上。现在，您可以乘坐这对总线中的第一个总线，并获得它所需的图像信息。

SELECT i1.image_id,
       i1.image_link,
       b1.bus_id
       FROM bus b1
            INNER JOIN bus b2
                       ON b1.code = b2.code
            INNER JOIN image i1
                       ON i1.bus_id = b2.bus_id;

要排除表中已经存在的图像，例如插入丢失的图像，只需添加一个带有相关子查询的NOT EXISTS，用于检查是否存在。

SELECT i1.image_id,
       i1.image_link,
       b1.bus_id
       FROM bus b1
            INNER JOIN bus b2
                       ON b1.code = b2.code
            INNER JOIN image i1
                       ON i1.bus_id = b2.bus_id
       WHERE NOT EXISTS (SELECT *
                                FROM image i2
                                WHERE i2.image_id = i1.image_id
                                      AND i2.image_link = i1.image_link
                                      AND i2.bus_id = b1.bus_id);

因此，INSERT将如下所示：

INSERT INTO image
            (image_id,
             image_link,
             bus_id)
SELECT i1.image_id,
       i1.image_link,
       b1.bus_id
       FROM bus b1
            INNER JOIN bus b2
                       ON b1.code = b2.code
            INNER JOIN image i1
                       ON i1.bus_id = b2.bus_id
       WHERE NOT EXISTS (SELECT *
                                FROM image i2
                                WHERE i2.image_id = i1.image_id
                                      AND i2.image_link = i1.image_link
                                      AND i2.bus_id = b1.bus_id);

db<>fiddle