题目描述
（通过次数5,465 | 提交次数7,995，通过率68.36%）
顾客表：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） 链接：https://leetcode.cn/problems/number-of-trusted-contacts-of-a-customer
//测试数据
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);
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');
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');
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');
解题思路
这道题涉及到的表比较多，表之间的关系也稍显复杂。需要耐心的审题。
Customers表保存了客户的基本信息。包括客户ID、客户姓名、Email地址；
Contacts表保存了客户的联系人信息。包括联系人的姓名、联系人的Email地址；
Invoices表保存了客户拥有的发票信息。包括发票ID、发票价格；
题目要求：查询每张发票信息，以及发票拥有人的姓名、联系人数量和可信联系人数量。
很明显，发票拥有人的姓名、联系人数量和可信联系人数量，跟发票本身是没有关系的。
发票拥有人的姓名，可以从Customers表直接获取。
发票拥有人的联系人数量，可以从Contacts表使用GROUP BY+COUNT汇总得出。
而发票拥有人的可信联系人数量，需要从Contacts表中过滤出Email存在于Customers表中的联系人，再使用GROUP BY+COUNT汇总得出。
最后，再以Invoices表作为主表，关联出对应的信息即可。
参考SQL
未特别说明的情况下，参考SQL为基于MySQL8.0实现。
with
tmp as (
select
a.user_id,
count(distinct a.contact_email) contacts_cnt,
count(distinct b.email) trusted_contacts_cnt
from Contacts a
left join Customers b
on a.contact_email = b.email
group by a.user_id
)
select
a.invoice_id,
b.customer_name,
a.price,
coalesce(c.contacts_cnt,0) contacts_cnt,
coalesce(c.trusted_contacts_cnt,0) trusted_contacts_cnt
from Invoices a
left join Customers b
on a.user_id = b.customer_id
left join tmp c
on a.user_id = c.user_id
order by a.invoice_id;
本站所有内容均为原创，本站保留所有权利。仅允许非商业用途的转载，但必须注明来源网站、作者、来源链接！否则，由此造成的一切后果，由转载方承担！
干货分享、技术提升、面试笔试、学习交流，欢迎关注公众号：xuesql。QQ学习交流群：209942678。
