选择一个满足不同记录中许多条件的列

问题描述：

我有这样的桌子:

+----------+---------+
| Customer | Product |
+----------+---------+
|        1 |       1 |
|        1 |       2 |
|        1 |       3 |
|        2 |       1 |
|        2 |       2 |
|        2 |       2 |
+----------+---------+

我想选择购买商品1 AND 2 AND 3的客户.因此我的查询应返回1.如何实现?

I would like to select Customer who bought Products 1 AND 2 AND 3. So my query should return 1. How to achieve that?

答

您可以在Customer上使用GROUP BY，并在Having子句中使用基于条件聚合的过滤.在数字上下文中使用时，MySQL自动将布尔值转换为0/1.

You can GROUP BY on the Customer and use conditional aggregation based filtering inside the Having clause. MySQL automatically casts boolean values to 0/1 when using in numeric context.

为了让Customer购买特定的Product，其SUM(Product = ..)应该为 1 .

In order to have a specific Product bought by a Customer, its SUM(Product = ..) should be 1.

案例1:吸引那些至少购买了1,2,3产品中的每一个的客户(他们可能也购买了其他产品).

Case 1: Get those customers which have purchased each one of the 1,2,3 products at-least (they may have purchased other products as well).

SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND -- 1 should be bought
       SUM(Product = 2) AND -- 2 should be bought
       SUM(Product = 3)     -- 3 should be bought

如果您想排他性地即，则客户除了1,2,3之外没有购买任何其他产品；那么您可以改用以下内容.

If you want exclusivity, i.e., the customer has not bought any other product other than 1,2,3; then you can use the following instead.

案例2:吸引那些只购买了 1,2,3个产品并且至少每个购买了一次的客户.

Case 2: Get those customers which have purchased only 1,2,3 products and each one of them has been purchased at-least once.

SELECT Customer
FROM your_table
GROUP BY Customer
HAVING SUM(Product = 1) AND  -- 1 should be bought
       SUM(Product = 2) AND  -- 2 should be bought
       SUM(Product = 3) AND  -- 3 should be bought
       NOT SUM(Product NOT IN (1,2,3))  -- anything other 1,2,3 should not be bought

选择一个满足不同记录中许多条件的列

相关推荐