Блог

Главная - Вопросы по программированию - Как скопировать несколько строк, принадлежащих одному идентификатору, и добавить ко всем другим идентификаторам в PostgreSQL

Как скопировать несколько строк, принадлежащих одному идентификатору, и добавить ко всем другим идентификаторам в PostgreSQL

#sql #postgresql

#sql #postgresql

Вопрос:

Итак, у меня есть табличные шины с уникальными идентификаторами и кодом:

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

Теперь для bus_id у меня есть эти изображения с их идентификаторами в другой таблице, например, изображения:

 | 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_id с тем же кодом из таблиц buses создавались повторяющиеся строки в таблицах images для всех шин в каждом коде шины.

Итак, результат, который я ищу в приведенных выше примерах:

 | 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 из табличных шин)

Ответ №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;

Комментарии:

1. Спасибо, но как мне обновить таблицу с результатом?

2. @WebOrNative . . . Добавьте insert перед select — вместе с соответствующими столбцами.

3. Спасибо, да, извините, я полный новичок.

Ответ №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;

Чтобы исключить изображения, которые уже есть в таблице, например, для вставки отсутствующих, просто добавьте a 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<>скрипка

Метки: