如何从SQL连接计算可用数量

Question

与加入Postgres数据库的查询，我很不幸。

我必须找出具有给定情况的可用数量。

我有4个表（分配，订单，订单词，材料）：

• 分配表有3列cntr_id作为承包商，matid as材料，qty_given

• OrderItems表具有Matid，Qty_used，OrderID

• order> orders表具有cntr_id，orderd

我的查询是在每个承包商中找到可用的库存。

我认为加入桌子以获得所需的结果非常容易，但这并不容易。

我尝试了许多连接的组合，但没有收益，因此我需要您的帮助。

让我们看看一个示例 - 我的分配表看起来像

select cntr_id, matid, qty_given 
from allocate

上述查询的结果

此查询返回所有承包商Matid和Qty_given的列表。

此处的查询返回cntr_id，matid和qty_used：

select 
    o.cntr_id, i.matid, i.untsreq_perorder used
from
    orderitems i 
join 
    orders o on i.orderid = o.orderid
join 
    material m on m.matid = i.matid
group by 
    o.cntr_id, i.untsreq_perorder, i.matid

上述查询的结果

此查询返回 仅在表格表中使用的那些值的列表。通过此查询，我可以通过他们找到所有承包商和qty_used。

现在，主要问题是如何使用第二个查询来与分配的表中的承包商获取可用库存。

我使用了几种连接组合，但没有任何成功。

我要加入的

select 
    a.cntr_id, a.matid, x.used
from
    allocate a
left join 
    (select o.cntr_id, i.matid, i.untsreq_perorder used
     from orderitems i 
     join orders o on i.orderid = o.orderid
     join material m on m.matid = i.matid
     group by o.cntr_id, i.untsreq_perorder, i.matid) x on a.cntr_id = x.cntr_id
group by 
    a.cntr_id, a.matid, x.used

结果就是这样：

上述查询的结果

请帮助我查找适用于承包商数量的资格查询。

我非常困惑

Answer 1

感谢您的支持，顺便说一句，我弄清楚了获取所需结果的方法。

select 
  a.matid, 
  a.cntr_id, 
  a.qty_given, 
  coalesce(sum(i.untsreq_perorder), 0) used, 
  coalesce((a.qty_given - i.untsreq_perorder),0) as available 
from 
  allocate a 
  left join orders o on o.cntr_id = a.cntr_id 
  left join material m on m.matid = a.matid 
  left join orderitems i on i.orderid = o.orderid 
  and i.matid = m.matid 
group by 
  a.matid, 
  a.cntr_id, 
  a.qty_given, 
  i.untsreq_perorder 
order by 
  a.cntr_id desc

挽救了我生命的主要逻辑是使用

left join orderitems i on i.orderid = o.orderid 
      and i.matid = m.matid 

您可以在

。 //www.db-fiddle.com/f/vscsrledr6vu582xwwv3f4/1

谢谢。