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

我有一个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

最佳答案

你的解决方案并不能真正解决问题。您需要按订单对结果进行分组，同时不要将不同订单中的订购项混淆起来，然后检索唯一的客户：

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

例如，如果从用户的一个订单中删除列表(1,2,3)（例如：下面）中的一个项目，那么在您的解决方案中，仍然会在输出中限定用户101，因为他已按不同的顺序订购了项目101。这种行为会使你失去接近的资格。

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

请参阅modifiedSQL Fiddle以查看我提到的问题。

关于mysql - 获取所有在同一订单中购买了三个指定商品的客户，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39550663/