# 1364.(Medium)顾客的可信联系人数量 顾客表：Customers ``` +---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | customer_name | varchar | | email | varchar | +---------------+---------+ customer_id 是这张表的主键。 此表的每一行包含了某在线商店顾客的姓名和电子邮件。 ``` 联系方式表：Contacts ``` +---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | id | | contact_name | varchar | | contact_email | varchar | +---------------+---------+ (user_id, contact_email) 是这张表的主键。 此表的每一行表示编号为 user_id 的顾客的某位联系人的姓名和电子邮件。 此表包含每位顾客的联系人信息，但顾客的联系人不一定存在于顾客表中。 ``` 发票表：Invoices ``` +--------------+---------+ | Column Name | Type | +--------------+---------+ | invoice_id | int | | price | int | | user_id | int | +--------------+---------+ invoice_id 是这张表的主键。 此表的每一行分别表示编号为 user_id 的顾客拥有有一张编号为 invoice_id、价格为 price 的发票。 ``` 为每张发票 invoice\_id 编写一个SQL查询以查找以下内容： customer\_name：与发票相关的顾客名称。 price：发票的价格。 contacts\_cnt：该顾客的联系人数量。 trusted\_contacts\_cnt：可信联系人的数量：既是该顾客的联系人又是商店顾客的联系人数量（即：可信联系人的电子邮件存在于客户表中）。 将查询的结果按照 invoice\_id 排序。 查询结果的格式如下例所示： ``` Customers table: +-------------+---------------+--------------------+ | customer_id | customer_name | email | +-------------+---------------+--------------------+ | 1 | Alice | alice@leetcode.com | | 2 | Bob | bob@leetcode.com | | 13 | John | john@leetcode.com | | 6 | Alex | alex@leetcode.com | +-------------+---------------+--------------------+ Contacts table: +-------------+--------------+--------------------+ | user_id | contact_name | contact_email | +-------------+--------------+--------------------+ | 1 | Bob | bob@leetcode.com | | 1 | John | john@leetcode.com | | 1 | Jal | jal@leetcode.com | | 2 | Omar | omar@leetcode.com | | 2 | Meir | meir@leetcode.com | | 6 | Alice | alice@leetcode.com | +-------------+--------------+--------------------+ Invoices table: +------------+-------+---------+ | invoice_id | price | user_id | +------------+-------+---------+ | 77 | 100 | 1 | | 88 | 200 | 1 | | 99 | 300 | 2 | | 66 | 400 | 2 | | 55 | 500 | 13 | | 44 | 60 | 6 | +------------+-------+---------+ Result table: +------------+---------------+-------+--------------+----------------------+ | invoice_id | customer_name | price | contacts_cnt | trusted_contacts_cnt | +------------+---------------+-------+--------------+----------------------+ | 44 | Alex | 60 | 1 | 1 | | 55 | John | 500 | 0 | 0 | | 66 | Bob | 400 | 2 | 0 | | 77 | Alice | 100 | 3 | 2 | | 88 | Alice | 200 | 3 | 2 | | 99 | Bob | 300 | 2 | 0 | +------------+---------------+-------+--------------+----------------------+ Alice 有三位联系人，其中两位(Bob 和 John)是可信联系人。 Bob 有两位联系人, 他们中的任何一位都不是可信联系人。 Alex 只有一位联系人(Alice)，并是一位可信联系人。 John 没有任何联系人。 ``` 来源：力扣（LeetCode） 链接： 著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。 ## Solution `Invoices` left join `Customers` left join `Contacts` ```sql SELECT invoice_id, max(cus.customer_name) customer_name, max(price) price, count(DISTINCT con.contact_email) contacts_cnt, IFNULL(sum(con.contact_email in (SELECT email FROM Customers)),0) trusted_contacts_cnt from Invoices i left JOIN Customers cus on i.user_id = cus.customer_id LEFT JOIN Contacts con on i.user_id = con.user_id GROUP BY invoice_id; ``` ## Table Schema ```sql Create table If Not Exists Customers (customer_id int, customer_name varchar(20), email varchar(30)); Create table If Not Exists Contacts (user_id int, contact_name varchar(20), contact_email varchar(30)); Create table If Not Exists Invoices (invoice_id int, price int, user_id int); Truncate table Customers; insert into Customers (customer_id, customer_name, email) values ('1', 'Alice', 'alice@leetcode.com'); insert into Customers (customer_id, customer_name, email) values ('2', 'Bob', 'bob@leetcode.com'); insert into Customers (customer_id, customer_name, email) values ('13', 'John', 'john@leetcode.com'); insert into Customers (customer_id, customer_name, email) values ('6', 'Alex', 'alex@leetcode.com'); Truncate table Contacts; insert into Contacts (user_id, contact_name, contact_email) values ('1', 'Bob', 'bob@leetcode.com'); insert into Contacts (user_id, contact_name, contact_email) values ('1', 'John', 'john@leetcode.com'); insert into Contacts (user_id, contact_name, contact_email) values ('1', 'Jal', 'jal@leetcode.com'); insert into Contacts (user_id, contact_name, contact_email) values ('2', 'Omar', 'omar@leetcode.com'); insert into Contacts (user_id, contact_name, contact_email) values ('2', 'Meir', 'meir@leetcode.com'); insert into Contacts (user_id, contact_name, contact_email) values ('6', 'Alice', 'alice@leetcode.com'); Truncate table Invoices; insert into Invoices (invoice_id, price, user_id) values ('77', '100', '1'); insert into Invoices (invoice_id, price, user_id) values ('88', '200', '1'); insert into Invoices (invoice_id, price, user_id) values ('99', '300', '2'); insert into Invoices (invoice_id, price, user_id) values ('66', '400', '2'); insert into Invoices (invoice_id, price, user_id) values ('55', '500', '13'); insert into Invoices (invoice_id, price, user_id) values ('44', '60', '6'); ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://zqt0.gitbook.io/leetcode/sql/1364.number-of-trusted-contacts-of-a-customer.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.