Medium之1364. 顾客的可信联系人数量***

野性酷女 2022-08-31 00:50 13阅读 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> 
  <tr> 
   <td>email</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

customer\_id 是这张表的主键。  
此表的每一行包含了某在线商店顾客的姓名和电子邮件。

**联系方式表：Contacts**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>user_id</td> 
   <td>id</td> 
  </tr> 
  <tr> 
   <td>contact_name</td> 
   <td>varchar</td> 
  </tr> 
  <tr> 
   <td>contact_email</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

(user\_id, contact\_email) 是这张表的主键。  
此表的每一行表示编号为 user\_id 的顾客的某位联系人的姓名和电子邮件。  
此表包含每位顾客的联系人信息，但顾客的联系人不一定存在于顾客表中。

**发票表：Invoices**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>invoice_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>price</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>user_id</td> 
   <td>int</td> 
  </tr> 
 </tbody> 
</table>

invoice\_id 是这张表的主键。  
此表的每一行分别表示编号为 user\_id 的顾客拥有有一张编号为 invoice\_id、价格为 price 的发票。

### 问题 ###

为每张发票 invoice\_id 编写一个SQL查询以查找以下内容：

customer\_name：与发票相关的顾客名称。  
price：发票的价格。  
contacts\_cnt：该顾客的联系人数量。  
trusted\_contacts\_cnt：可信联系人的数量：既是该顾客的联系人又是商店顾客的联系人数量（即：可信联系人的电子邮件存在于客户表中）。  
将查询的结果按照 invoice\_id 排序。

### 示例 ###

**Customers table:**

<table> 
 <thead> 
  <tr> 
   <th>customer_id</th> 
   <th>customer_name</th> 
   <th>email</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Alice</td> 
   <td>alice@leetcode.com</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Bob</td> 
   <td>bob@leetcode.com</td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td>John</td> 
   <td>john@leetcode.com</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Alex</td> 
   <td>alex@leetcode.com</td> 
  </tr> 
 </tbody> 
</table>

**Contacts table:**

<table> 
 <thead> 
  <tr> 
   <th>user_id</th> 
   <th>contact_name</th> 
   <th>contact_email</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Bob</td> 
   <td>bob@leetcode.com</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>John</td> 
   <td>john@leetcode.com</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Jal</td> 
   <td>jal@leetcode.com</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Omar</td> 
   <td>omar@leetcode.com</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Meir</td> 
   <td>meir@leetcode.com</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Alice</td> 
   <td>alice@leetcode.com</td> 
  </tr> 
 </tbody> 
</table>

**Invoices table:**

<table> 
 <thead> 
  <tr> 
   <th>invoice_id</th> 
   <th>price</th> 
   <th>user_id</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>77</td> 
   <td>100</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>88</td> 
   <td>200</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>99</td> 
   <td>300</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>66</td> 
   <td>400</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>55</td> 
   <td>500</td> 
   <td>13</td> 
  </tr> 
  <tr> 
   <td>44</td> 
   <td>60</td> 
   <td>6</td> 
  </tr> 
 </tbody> 
</table>

**Result table:**

<table> 
 <thead> 
  <tr> 
   <th>invoice_id</th> 
   <th>customer_name</th> 
   <th>price</th> 
   <th>contacts_cnt</th> 
   <th>trusted_contacts_cnt</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>44</td> 
   <td>Alex</td> 
   <td>60</td> 
   <td>1</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>55</td> 
   <td>John</td> 
   <td>500</td> 
   <td>0</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>66</td> 
   <td>Bob</td> 
   <td>400</td> 
   <td>2</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>77</td> 
   <td>Alice</td> 
   <td>100</td> 
   <td>3</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>88</td> 
   <td>Alice</td> 
   <td>200</td> 
   <td>3</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>99</td> 
   <td>Bob</td> 
   <td>300</td> 
   <td>2</td> 
   <td>0</td> 
  </tr> 
 </tbody> 
</table>

Alice 有三位联系人，其中两位(Bob 和 John)是可信联系人。  
Bob 有两位联系人, 他们中的任何一位都不是可信联系人。  
Alex 只有一位联系人(Alice)，并是一位可信联系人。  
John 没有任何联系人。

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/number-of-trusted-contacts-of-a-customer  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

select invoice_id,
            customer_name,
            price,
            count(c2.user_id) as contacts_cnt,
            count(case when c2.contact_email in (select email from customers) 
                       then 1 else null 
                  end) trusted_contacts_cnt 
    from 
            invoices v
            left join 
            customers c1 on v.user_id = c1.customer_id 
            left join 
            contacts c2 on v.user_id = c2.user_id
    group by invoice_id
    order by invoice_id

注意：计算可信联系人的数量时，不符合条件的要令为Null,而不能是0，因为前面用到的是count;如果前面用到的是sum,则不符合条件的要令为0.