Создание много ко многим в postgresql

Question

У меня есть две таблицы, с которыми мне нужно установить отношения многие ко многим. Одна таблица, которую мы назовем инвентаризацией, заполняется через форму. Продажи других таблиц заполняются путем импорта CSV в базу данных еженедельно.

Пример таблицы изображений

Я хочу пройтись по таблице продаж и связать каждую строку продажи со строкой с тем же номером в таблице инвентаря. Вот этот удар. Мне нужно связать только количество строк продаж, указанных в поле Количество каждой строки инвентаря.

Пример: пример изображения связанных таблиц

Теперь я знаю, что могу сделать это, создав Perl-скрипт, который просматривает таблицу продаж и создает ссылки, используя поле ItemIDUniqueKey в цикле на основе поля "Количество". Я хочу знать, есть ли способ сделать это, используя только команды SQL? Я много читал о многих для многих, и я не нашел никого, кто делал бы это.

Answer 1

Предполагая таблицы:

create table a(
    item_id integer,
    quantity integer,
    supplier_id text,
    sku text
);

а также

create table b(
    sku text,
    sale_number integer,
    item_id integer
);

следующий запрос, кажется, делает то, что вы хотите:

update b b_updated set item_id = (
    select item_id 
    from (select *, sum(quantity) over (partition by sku order by item_id) as sum from a) a 
    where 
        a.sku=b_updated.sku and 
        (a.sum)>
            (select count(1) from b b_counted 
            where 
                b_counted.sale_number<b_updated.sale_number and
                b_counted.sku=b_updated.sku
            )
    order by a.sum asc limit 1
    );