用不存在的行完成表

DroppingOff 发表于 Dev

下车

我有一个这样的表：

buyer        product        quantity
tom          skirt          2
anna         skirt          3
tom          jeans          5

在distinct(product) = skirt与jeans

我想要一个表，quantity = 0当<buyer, product>元组对于所有可能的产品都不存在时，插入另一列。

因此结果将是：

buyer        product        quantity
tom          skirt          2
anna         skirt          3
tom          jeans          5
anna         jeans          0

它看起来并不复杂，但是我不知道该怎么做。

更新
我发现了一个额外的复杂性。

我的产品实际上由两个字段定义：类和产品。product可以为null，并且product字段为null时（我正在通过交叉联接进行操作），我不必丢失信息量。

所以，如果我有这个：

buyer       class       product        quantity
tom         clothes     skirt          2
anna        clothes     skirt          3
tom         clothes     jeans          5
jim         shoes       NULL           7

我会需要：

buyer       class       product        quantity
tom         clothes     skirt          2
anna        clothes     skirt          3
tom         clothes     jeans          5
anna        clothes     jeans          0
jim         shoes       NULL           7
jim         clothes     skirt          0
jim         clothes     jeans          0
tom         shoes       NULL           0
anna        shoes       NULL           0

谢谢大家，我知道我正在使事情复杂化！

戈登·利诺夫

您可以使用cross join生成买家和产品的所有可能组合。然后使用left join（或not exists）过滤掉表格中已经存在的内容：

insert into table(buyer, product, quantity)
    select b.buyer, p.product, 0
    from (select distinct buyer from table) b cross join
         (select distinct product p from table) p left join
         table t
         on t.buyer = b.buyer and t.product = p.product
    where t.buyer is null;

编辑：

如果要返回所有行的查询，则可以使用类似的方法：

    select b.buyer, p.product, coalesce(t.qty, 0) as qty
    from (select distinct buyer from table) b cross join
         (select distinct product p from table) p left join
         table t
         on t.buyer = b.buyer and t.product = p.product;

编辑二：

如果您有NULL购买者和/或产品的价值，请使用NULL安全的比较：

    select b.buyer, p.product, coalesce(t.qty, 0) as qty
    from (select distinct buyer from table) b cross join
         (select distinct product p from table) p left join
         table t
         on t.buyer is not distinct from b.buyer and
            t.product is not distinct from p.product;

（作为一个小小的注解：我真的不喜欢distinct在此结构中使用。为什么Postgres（ANSI？）赋予它如此复杂的名称？）

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。