MySQL查询,查找订购了两种特定产品的客户

我按照以下方式进行此类查询:

SELECT COUNT(DISTINCT t1.userid) AS user_count
  FROM TRANSACTIONS t1
  JOIN TRANSACTIONS t2 USING (userid)
 WHERE t1.product_id = 'prod1' 
   AND t2.product_id = 'prod2';

这 GROUP BY shown @najmeddine也会生成您想要的答案,但它在MySQL上的表现不佳。MySQL很难优化

EXPLAIN

SELECT userid
  FROM TRANSACTIONS
 WHERE product_id in ('prod1', 'prod2')
GROUP BY userid
HAVING COUNT(DISTINCT product_id) = 2

(使用用户提供的附加信息在下面添加了新选项)

尝试

SELECT * FROM Customers WHERE 
 EXISTS (SELECT * FROM Purchases WHERE ProductID = 'PROD1' AND CustID = Customers.CustID)
 AND
 EXISTS (SELECT * FROM Purchases WHERE ProductID = 'PROD2' AND CustID = Customers.CustID)

或

SELECT * FROM Customers WHERE 
 CustID IN (SELECT CustID FROM Purchases WHERE ProductID = 'PROD1')
 AND
 CustID IN (SELECT CustID FROM Purchases WHERE ProductID = 'PROD2')

或

SELECT UserID FROM Transactions WHERE ProductID = 'PROD1'
 AND EXISTS (SELECT * FROM Transactions WHERE UserID = T1.UserID 
  AND ProductID = 'PROD2')

或

SELECT UserID FROM Transactions WHERE ProductID = 'PROD1'
 AND UserID IN (SELECT UserID FROM Transactions WHERE ProductID = 'PROD2')

SELECT o.CustomerID, Sum([ProductID]='Prod1') AS Expr1, Sum([productid]='Prod1') AS Expr2
FROM Orders AS o INNER JOIN [Order Details] AS d ON o.OrderID = d.OrderID
GROUP BY o.CustomerID
HAVING (((Sum([ProductID]='Prod1'))<>0) AND ((Sum([productid]='Prod1'))<>0));

SELECT COUNT(DISTINCT userId)
FROM(
  SELECT userId
  FROM transactions
  WHERE product = 'PROD1'
  INTERSECT
  SELECT userId
  FROM transactions
  WHERE product = 'PROD2');

交集运算符返回一个表,该表仅包含前两个表中的行,即购买了这两种产品的人。

sakila db示例:

SELECT R.customer_id, GROUP_CONCAT(I.film_id) 
FROM sakila.rental R 
RIGHT OUTER JOIN sakila.inventory I ON R.inventory_id = I.inventory_id 
WHERE I.film_id IN (22,44) GROUP BY R.customer_id HAVING COUNT(*) = 2