选择一个满足不同记录中许多条件的列 [英] Choosing a column that fulfills many conditions in different records

问题描述

我有这样的桌子:

+----------+---------+
| 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

这篇关于选择一个满足不同记录中许多条件的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！