MySQL 查找包含特定产品的所有订单，并且仅查找该产品

Question

我有两张 table

ORDERS
id
customer_name

和

ORDER_LINE_ITEMS
id
orders_id
product_id

如何查找仅包含一个商品的所有订单，特别是在订购 product_id = 2 的地方？

Answer 1

可以像这样简单地提取:

SELECT * FROM ORDERS WHERE ID IN
    (SELECT ORDERS_ID FROM ORDER_LINE_ITEMS 
         WHERE PRODUCT_ID = 2
         GROUP BY ORDERS_ID
         HAVING COUNT(ORDERS_ID) = 1
    )

如果您希望在您的评论中只有 Product_id = 2 的订单并且没有任何其他产品，那么 sql 应该是这样的:

SELECT * FROM ORDERS WHERE ID IN
    (SELECT ORDERS_ID FROM ORDER_LINE_ITEMS 
         WHERE PRODUCT_ID = 2
         GROUP BY ORDERS_ID
         HAVING COUNT(ORDERS_ID) = 1
    )
and id not in
    (SELECT ORDERS_ID FROM ORDER_LINE_ITEMS 
         WHERE PRODUCT_ID <> 2
    )