MySQL查询以查找已订购两种特定产品的客户 [英] MySQL Query to find customers who have ordered two specific products

问题描述

我很难提出一个查询，该查询将找到同时购买了PROD1和PROD2的所有客户.

I'm having trouble coming up with a query that will find all customers who have purchased both PROD1 and PROD2.

这是一个伪查询，看起来像我想要做的:(显然这是行不通的)

Here's a pseudo-query that kind of looks like what I want to do: (obviously this wouldn't work)

SELECT COUNT(DISTINCT userid) 
  FROM TRANSACTIONS 
 WHERE product_id = 'prod1' 
   AND product_id = 'prod2'

因此，基本上，我试图获取在transactions表中具有product_id'prod1'和'prod2'事务的不同用户ID的数量的计数.每个交易都存储在transactions表中的一行中.

So basically I'm trying to get a count of the number of distinct userids that have a transaction in the transactions table for both product_id 'prod1' and 'prod2'. Each transaction is stored in a row in the transactions table.

推荐答案

我通过以下方式进行此类查询:

I do this type of query in the following way:

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解决方案

The GROUP BY solution shown by @najmeddine also produces the answer you want, but it doesn't perform as well on MySQL. MySQL has a hard time optimizing GROUP BY queries.

您应该尝试两个查询，并使用 EXPLAIN分析优化，并根据数据库中的数据量运行一些测试并计时结果.

You should try both queries, analyzing the optimization with EXPLAIN, and also run some tests and time the results given the volume of data in your database.

这篇关于MySQL查询以查找已订购两种特定产品的客户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！