PostgreSQL查询聚合和RIGHT JOIN不过滤

我在一对多地图,库存和批次上有两个表,在批次表上我有一个名为deleted的布尔字段,所以我没有真正删除行,但更改了要忽略的标志而不计入查询,我正在尝试将一个查询合并到一个视图中,但是如果我添加了删除的字段,那么查询的RIGHT JOIN就像INNER JOIN一样,我希望下面的示例中的所有FROM库存工作正常,但不排除任何已删除的记录在桌子上jap.lots.

    CREATE VIEW view_inventory_lots AS 
SELECT count(lots.*) AS lots,
sum(lots.qty_available) AS available,
sum(lots.qty_received) AS received,
sum(lots.qty_on_hand) AS onhand,
sum(lots.qty_allocated) AS allocated,
inventories.* 
FROM jap.lots RIGHT JOIN jap.inventories
 ON jap.lots.inventory_id = jap.inventories.inventory_id
  GROUP BY inventories.inventory_id;

如果我尝试修改此视图以使用以下查询添加lot.deleted字段进行过滤:

SELECT count(lots.*) AS lots,
sum(lots.qty_available) AS available,
sum(lots.qty_received) AS received,
sum(lots.qty_on_hand) AS onhand,
sum(lots.qty_allocated) AS allocated,
lots.deleted,
inventories.* 
FROM jap.lots RIGHT JOIN jap.inventories
 ON jap.lots.inventory_id = jap.inventories.inventory_id
 WHERE lots.deleted = false
  GROUP BY inventories.inventory_id, lots.deleted;

结果只是在批次表上有记录的库存行,因此忽略了RIGHT JOIN的用途,表现为INNER JOIN

有任何想法吗？提前致谢

使用where子句中"外部"表中的列将外部联接转换为内部联接.将该表上的条件移动到以下join条件:

FROM jap.lots 
  RIGHT JOIN jap.inventories 
     ON jap.lots.inventory_id = ap.inventories.inventory_id
    AND lots.deleted = false
GROUP BY ...