Postgres在联接表上有n个约束的不同内部联接(一对多)

Question

我正在努力寻找执行Postgres查询的最有效方法。

我有两张桌子

• 订单(id，order_name(
• 项目(id、order_id、item_name(

订单表中的每个订单在项目表中都有1到n个项目。

我想要的是执行一个查询，返回包含汉堡、披萨和可乐的所有订单。

下面的调用是进行此类查询的最佳方式吗？

SELECT DISTINCT ON (orders.id)
orders.id
FROM orders
INNER JOIN items i1 on orders.id = i1.order_id
INNER JOIN items i2 on orders.id = i2.order_id
INNER JOIN items i3 on orders.id = i3.order_id
WHERE i1.item_name = 'Burger'
AND i2.item_name = 'Fries'
AND i3.item_name = 'Coke'

Answer 1

您的方法是有效的，并且应该是相对高效的。假设没有重复的订单id/项目名称，您可以从其他查询中删除distinct，这样可以避免不必要的工作。

您也可以使用聚合。当项目数量增加时，它需要更少的额外打字：

select o.*
from orders o
inner join items i on i.order_id = o.id
where i.item_name in ('Burger', 'Fries', 'Coke')  -- any of the three items
group by o.order_id
having count(*) = 3                               -- all items match

同样，这假设项目中没有重复项；否则，您需要having count(distinct i.item_name) = 3。