sql - 带有聚合和 RIGHT JOIN 的 PostgreSQL 查询不过滤

Question

我在一对多 map 上有两个表，inventory 和 lots，在 lots 表上我有一个名为 deleted 的 bool 字段，所以我不会真正删除行，但将标志更改为忽略而不是计数查询，我正在尝试合并一个 View 的查询，但如果我添加已删除的字段，则查询的 RIGHT JOIN 行为类似于 INNER JOIN，我希望下面示例中的 ALL FROM list 工作正常但不排除表 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;

如果我尝试修改此 View 以添加 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;

结果只是在 lots 表上有记录的 inventory 行，所以忽略了 RIGHT JOIN 的目的，表现为 INNER JOIN

有什么想法吗？提前致谢

Answer 1

在 where 子句中使用来自“外部”表的列将外部联接转换为内部联接。将该表的条件移动到 join 条件中:

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