这是一个关系划分问题。
查找所有产品都属于给定类型的订单的一种解决方案是：

SELECT *
FROM orders
INNER JOIN order_items ON order_items.order_id = orders.id
INNER JOIN products ON products.id = order_items.product_id
WHERE orders.id IN (
    SELECT order_items.order_id
    FROM order_items
    INNER JOIN products ON products.id = order_items.product_id
    GROUP BY order_items.order_id
    HAVING COUNT(CASE WHEN products.type = 'FooProduct' THEN 1 END) = COUNT(*)
)

稍微调整一下上面的内容，找到所有产品都来自给定类型列表的订单是这样的：

HAVING COUNT(CASE WHEN products.type IN ('FooProduct', 'BarProduct') THEN 1 END) = COUNT(*)

要查找所有产品与给定列表中所有类型匹配的所有订单，请执行以下操作：

HAVING COUNT(CASE WHEN products.type IN ('FooProduct', 'BarProduct') THEN 1 END) = COUNT(*)
AND    COUNT(DISTINCT products.type) = 2

干扰测试

您可以使用 Having 以及基于条件聚集函数的过滤。 products.type IN ('FooProduct', 'BarProduct') 如果一个产品类型不是其中任何一个，则返回0。我们可以用 Sum() 函数，用于进一步过滤。
请尝试以下操作：

SELECT orders.order_id 
FROM orders 
INNER JOIN order_items ON order_items.order_id = orders.id   
INNER JOIN products ON products.id = order_items.product_id 
GROUP BY orders.order_id 
HAVING SUM(products.type IN ('FooProduct', 'BarProduct')) < COUNT(*)

在这种情况下，您要寻找的订单只有 FooProduct 类型，则可以使用以下内容：

SELECT orders.order_id 
FROM orders 
INNER JOIN order_items ON order_items.order_id = orders.id   
INNER JOIN products ON products.id = order_items.product_id 
GROUP BY orders.order_id 
HAVING SUM(products.type <> 'FooProduct') = 0

另一种可能的方法是：

SELECT orders.order_id 
FROM orders 
INNER JOIN order_items ON order_items.order_id = orders.id   
INNER JOIN products ON products.id = order_items.product_id 
GROUP BY orders.order_id 
HAVING SUM(products.type = 'FooProduct') = COUNT(*)

我建议在joined subselect中使用count（distinct），如下所示：

SELECT orders.*
FROM orders 
inner join (
    SELECT orderid, max(products.type) as products_type
    FROM order_items
    INNER JOIN products ON products.id = order_items.product_id
    GROUP BY orderid
    -- distinct count of different products = 1 
    --    -> all order items are for the same product type
    HAVING COUNT(distinct products.type ) = 1 
    -- alternative is:
    -- min(products.type )=max(products.type )
) as tmp on tmp.orderid=orders.orderid 
WHERE 1=1
-- if you want only single type product orders for some specific product
and tmp.products_type = 'FooProduct'

可以使用聚合和 having 条款：

SELECT o.*
FROM orders o INNER JOIN
     order_items oi
     ON oi.order_id = o.id INNER JOIN
     products p
     ON p.id = oi.product_id   
GROUP BY o.id  -- OK assuming `id` is the primary key
HAVING SUM(p.type NOT IN ('FooProduct', 'BarProduct')) > 0;  -- at least one other product

事实上，这并不完全正确。这会得到一些其他产品的订单，但它不会接受只有foo和bar混合的订单。我想这会让其他人：

HAVING SUM(p.type = 'FooProduct') < COUNT(*) AND
       SUM(p.type = 'BarProduct') < COUNT(*)

这是一个基本的解决方案，虽然效率不高，但很简单：

SELECT * FROM orders WHERE id NOT IN (
    SELECT orders.id FROM orders 
    INNER JOIN order_items ON order_items.order_id = orders.id   
    INNER JOIN products ON products.id = order_items.product_id     
    WHERE products.type = 'FooProduct'
    AND (
      NOT EXISTS (
        SELECT null
        FROM products
        INNER JOIN order_items ON order_items.product_id = products.id
        WHERE order_items.order_id = orders.id
        AND products.type != 'FooProduct'
      )
 )
) AND id NOT IN (
    SELECT orders.id FROM orders 
    INNER JOIN order_items ON order_items.order_id = orders.id   
    INNER JOIN products ON products.id = order_items.product_id     
    WHERE products.type = 'BarProduct'
    AND (
      NOT EXISTS (
        SELECT null
        FROM products
        INNER JOIN order_items ON order_items.product_id = products.id
        WHERE order_items.order_id = orders.id
        AND products.type != 'BarProduct'
      )
 )
)