Medium之1398.购买了产品A和产品B却没有购买C的产品

矫情吗；* 2022-08-31 04:46 149阅读 0赞

**Customers 表：**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>customer_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>customer_name</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

customer\_id 是这张表的主键。  
customer\_name 是顾客的名称。

**Orders 表：**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>order_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>customer_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>product_name</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

order\_id 是这张表的主键。  
customer\_id 是购买了名为 “product\_name” 产品顾客的id。

### 问题 ###

请你设计 SQL 查询来报告购买了产品 A 和产品 B 却没有购买产品 C 的顾客的 ID 和姓名（ customer\_id 和 customer\_name ），我们将基于此结果为他们推荐产品 C 。  
您返回的查询结果需要按照 customer\_id 排序。

### 示例 ###

**Customers table:**

<table> 
 <thead> 
  <tr> 
   <th>customer_id</th> 
   <th>customer_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Daniel</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Diana</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Elizabeth</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Jhon</td> 
  </tr> 
 </tbody> 
</table>

**Orders table:**

<table> 
 <thead> 
  <tr> 
   <th>order_id</th> 
   <th>customer_id</th> 
   <th>product_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>10</td> 
   <td>1</td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>20</td> 
   <td>1</td> 
   <td>B</td> 
  </tr> 
  <tr> 
   <td>30</td> 
   <td>1</td> 
   <td>D</td> 
  </tr> 
  <tr> 
   <td>40</td> 
   <td>1</td> 
   <td>C</td> 
  </tr> 
  <tr> 
   <td>50</td> 
   <td>2</td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>60</td> 
   <td>3</td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>70</td> 
   <td>3</td> 
   <td>B</td> 
  </tr> 
  <tr> 
   <td>80</td> 
   <td>3</td> 
   <td>D</td> 
  </tr> 
  <tr> 
   <td>90</td> 
   <td>4</td> 
   <td>C</td> 
  </tr> 
 </tbody> 
</table>

**Result table:**

<table> 
 <thead> 
  <tr> 
   <th>customer_id</th> 
   <th>customer_name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>3</td> 
   <td>Elizabeth</td> 
  </tr> 
 </tbody> 
</table>

只有 customer\_id 为 3 的顾客购买了产品 A 和产品 B ，却没有购买产品 C 。

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/customers-who-bought-products-a-and-b-but-not-c  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

##### 法一：窗口函数 #####

select c.customer_id as customer_id,customer_name
    from orders o 
        left join 
        customers c on o.customer_id = c.customer_id
    group by o.customer_id
    having sum(product_name='A')>0 and sum(product_name='B')>0 and sum(product_name = 'C')=0

##### 法二：WHERE&IN #####

select customer_id,customer_name
    from customers
    where customer_id in (select customer_id from orders where product_name='A')
        and customer_id in (select customer_id from orders where product_name='B')
        and customer_id not in (select customer_id from orders where product_name='C')