mysql - 如果列只有两个特定值，sql select all

Question

orders (
  o_id INT AUTO_INCREMENT,
  o_status TINYINT,
  o_description VARCHAR(50),
)

orders_products (
  op_id INT AUTO_INCREMENT,
  op_order_id INT(11),
  op_product_id INT(11),
  op_price DECIMAL(19, 2),
)

如何选择所有只有 id = 1 和 id = 2 产品的订单。

谢谢你，对不起我的英语...

Answer 1

有多种方法可以获得所需的结果，这利用了条件聚合:

select *
from orders 
where o_id in
 (
   select op_order_id 
   from orders_products
   having count(case when op_product_id = 1 then 1 end) > 0 -- at least one row with 1
      and count(case when op_product_id = 2 then 1 end) > 0 -- at least one row with 2
      and count(case when op_product_id not in (1,2) then 1 end) = 0 -- no other value 
 )

根据索引/选择性 EXISTS/NOT EXISTS 可能更快:

select o_id
from orders as o
where exists (select * 
              from orders_products as op
              where op.op_order_id = o.o_id 
                and op.op_product_id = 1)  -- at least one row with 1
  and exists (select * 
              from orders_products as op
              where op.op_order_id = o.o_id 
                and op.op_product_id = 2)  -- at least one row with 2
  and not exists (select * 
                  from orders_products as op
                  where op.op_order_id = o.o_id 
                    and op.op_product_id not in (1,2))  -- no other value