力扣SQL笔试题100道,点此免费下载!!!

题目描述

(通过次数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;

力扣SQL笔试题100道,点此免费下载!!!

picture loss