在postgresql中创建多对多

Question

我有两个表，需要与它们建立多对多关系。我们称之为库存的一个表是通过表格填充的。另一个表销售是通过每周将CSV导入数据库来填充的。

示例表图像

我想遍历销售表，并将每个销售行与库存表中具有相同sku的行相关联。踢到了。我只需要关联每个"库存"行的"数量"字段中指示的销售行数。

示例：链接表的示例图像

现在我知道我可以通过创建一个perl脚本来实现这一点，该脚本遍历sales表，并在基于Quantity字段的循环中使用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
    );