mysql - 获取在同一订单中购买了三个指定商品的所有客户

Question

我有一个 customer_orders 表，如下所示，我想检索以相同顺序购买商品 'x','y','z' 的所有客户。只是想知道如何实现这个问题的解决方案。

输入数据:-

CREATE TABLE customer_orders
(
  customer_id INTEGER,
  order_id INTEGER,
  item_id INTEGER,
  ORDER_QTY INTEGER,
  PRICE INTEGER,
  ORDER_DATE DATE
 ); 

 INSERT INTO customer_orders VALUES(101,901,1,50,10,'2015-07-15'); 
 INSERT INTO customer_orders VALUES(101,901,2,20,50,'2015-07-15');
 INSERT INTO customer_orders VALUES(101,901,3,40,30,'2015-07-15'); 
 INSERT INTO customer_orders VALUES(102,902,2,30,15,'2015-04-16');
 INSERT INTO customer_orders VALUES(102,902,3,20,20,'2015-04-16');
 INSERT INTO customer_orders VALUES(103,903,1,50,10,'2014-05-17'); 
 INSERT INTO customer_orders VALUES(102,904,3,100,20,'2014-08-18'); 
 INSERT INTO customer_orders VALUES(101,905,2,200,15,'2014-09-19'); 
 INSERT INTO customer_orders VALUES(104,906,3,70,20,'2015-10-18'); 
 INSERT INTO customer_orders VALUES(104,906,2,30,15,'2015-10-18'); 
 INSERT INTO customer_orders VALUES(104,906,1,40,10,'2015-10-18'); 
 INSERT INTO customer_orders VALUES(105,907,2,80,15,'2014-03-29'); 
 INSERT INTO customer_orders VALUES(104,908,2,90,15,'2014-05-19'); 

输出:-

101
104

Assumption :- Item x --> 1, Item y ---> 2, Item z --> 3

Answer 1

您的解决方案实际上并没有解决问题。您还需要按订单对结果进行分组，不要混淆不同订单中的订购商品，然后检索唯一的客户:

SELECT DISTINCT 
         customer_id
FROM     customer_orders
WHERE    item_id BETWEEN 1 AND 3
GROUP BY customer_id, order_id
HAVING   COUNT( DISTINCT item_id ) = 3

---

例如，如果您从用户 101 的一个订单中删除列表 (1,2,3) 中的一项(例如:下面)，那么在你的解决方案它仍然会在输出中限定用户 101，因为他以不同的顺序订购了项目 2。这种行为会使您的方法不合格。

-- INSERT INTO customer_orders VALUES(101,901,2,20,50,'2015-07-15');

参见修改后的 SQL Fiddle查看我提到的问题。