SQL题目实战---力扣篇1---1364.（信任的顾客）

Sql_loverrrr

于 2025-04-10 13:29:38 发布

阅读量996

点赞数 37

分类专栏： sql习题实战文章标签： sql 数据库 oracle 后端

本文链接：https://blog.youkuaiyun.com/m0_50732667/article/details/147116294

版权

sql习题实战专栏收录该内容

4 篇文章

订阅专栏

首先我们来看一下这道题目：

顾客表：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 编写一个查询方案以查找以下内容：

customer_name：与发票相关的顾客名称。
price：发票的价格。
contacts_cnt：该顾客的联系人数量
trusted_contacts_cnt：可信联系人的数量：既是该顾客的联系人又是商店顾客的联系人数量（即：可信联系人的电子邮件存在于 Customers 表中）。

返回结果按照 invoice_id 排序。

结果的格式如下例所示。

示例 1：
输入：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       |
+------------+-------+---------+
输出：
+------------+---------------+-------+--------------+----------------------+
| 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 没有任何联系人。

以上是题目的内容，接下来我把表格代码放到下方便于大家建表

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')

好的，准备工作准备完毕，接下来我们开始分析题目要求：

首先看到，题目让我们根据发票的内容，找到上面用户的联系人有几个，并且还要从中进一步细化出被信任的人有几个。

这道题目主要考查了我们多表关联的能力，在我写的Oracle基础篇中有所提及，大家可以回头看一下，这里我们直接进行实战演练。

接下来我们梳理一下解题思路：根据发票表的user_id我们就可以对应到顾客表的customer_id ，

这样两表关联我们就可以得到输出表的前三列，如下：

到这里我们只用了一步关联，并且select后面选出我们所要的列就可以了

好的！我们用代码来实现一下：


select
    *
from INVOICES inv , Customers cust
where inv.user_id=cust.customer_id
order by inv.INVOICE_ID
;

运行结果：

这时我们其实就得到了前三列内容，那么难点来了，如何得到后两列内容呢？

我们再进行分析，第四列内容，也就是每个用户的联系人从哪里获取，是不是通过联系人表，也就是contact表就可以看到，user_id为1的人有三位联系人分别是：Bob John 和 Jal ，那么我们再找，哪个人的id是1呢？看第一个customer表我们知道，Alice的id是1，因此说明，Alice有三位联系人，那么在第四列Alice后面就应该填3。以此类推我们知道剩余几个人他们的联系人都有谁以及数量是多少。

好的！我们既然捋清楚了这一点，我们就可以开始统计了，使用分组聚合来统计每一个相同id分别有几行就可以了

代码如下

select
    user_id,
    count(1)
from CONTACTS
group by user_id
;

运行结果

这样我们就得到了第四列的数据。

有人问，有的人没有联系人，这块没有显示，那怎么办呢？

我们在下一步使用左连接，没有联系人的后面就会显示‘null’ ，我们再使用 nvl（）函数把‘null’变为0就可以了

with t1 as (
    select
    *
from INVOICES inv , Customers cust
where inv.user_id=cust.customer_id
order by inv.INVOICE_ID
)
,t2 as (
    select
    user_id,
    count(1) num
from CONTACTS
group by user_id
)
select
    t1.user_id,
    invoice_id,
    customer_name,
    price,
    nvl(num,0) contacts_cnt
from t1 left join t2 on t1.user_id=t2.user_id
;

运行结果如下：

ok！我们第四列也搞定了，接下来到了最复杂的第五列，如何从这些人中进一步筛选出是被信任的人呢？

根据题目要求，被信任者，首先必须要是顾客表里面的人，还要满足联系人表中有他才可以，举个例子来说，联系人表中 user_id 是 1 的人的联系人有三位鲍勃琼和乔，这个我们前面提到了，那进一步筛选，我们发现，顾客表中只有鲍勃和琼没有乔，所以只有鲍勃和琼才能算是被信任者，因此 1 号爱丽丝的被信任者只有两位，所以第五列要填 2 。

好的，我们梳理清楚了，那么接下来我们要怎么实现呢？

我们先用联系人表与顾客表中的人名进行关联，因为要找两个都符合的人名对吧，对应上了两个都符合的人名之后我们再去找他们的联系人是谁，再分组聚合不就求出来被信任者的个数了吗？

我们一步一步来：先关联人名

上图中看出，1号 id 的被信任者有俩，与我们上述内容相符合， 6号id的被信任者只有一位。

我们下一步把这个表再和之前的那个四列表左关联，让没有被信任者的 id 也显示出来为 null，然后我们再用 nvl 函数把null变成0即可得到最终答案

最终实现如下：

with t1 as (
    select
    *                                    ----关联两表把人关联上
from INVOICES inv , Customers cust
where inv.user_id=cust.customer_id
order by inv.INVOICE_ID
)
,t2 as (                              -------再搞一个统计联系人多少个的表
    select
    user_id,
    count(1) num
from CONTACTS
group by user_id
)
,t3 as (                           -------------关联上述两表得到除了信任用户的所有列
    select
    t1.user_id,
    invoice_id,
    customer_name,
    price,
    nvl(num,0) contacts_cnt
from t1 left join t2 on t1.user_id=t2.user_id    ------关联两表的名字得出信任用户有谁并统计数量
)
select
    invoice_id,
    customer_name,
    price,
    contacts_cnt,
    nvl(cnt2,0) trusted_contacts_cnt
from t3 left join (select                       ----------关联 上述综合表 把新人用户数量列加上 为null的得0
                    user_id,count(1) cnt2
                from  CONTACTS con join Customers cus on con.CONTACT_NAME=cus.customer_name
                group by user_id)  t4  on  t3.user_id=t4.user_id
;

最终在力扣测试，答案通过！

此题最重要的是梳理业务以及关联表内容，做好这两点就可以解题，综合难度不大。

结尾欢迎大家点赞收藏加评论，后续会持续更新题目，无需力扣plus会员也能做题 ~ ~ ~