SQL实战训练之，力扣：1949. 坚定的友谊

目录

        一、力扣原题链接

        二、题目描述

        三、建表语句

        四、题目分析        

        五、SQL解答

        六、最终答案

        七、验证

        八、知识点

---

一、力扣原题链接

1949. 坚定的友谊

二、题目描述

表名: Friendship

+-------------+------+
| 列名         | 类型 |
+-------------+------+
| user1_id    | int  |
| user2_id    | int  |
+-------------+------+
(user1_id, user2_id) 是这个表的主键（具有唯一值的列的组合）。
这张表的每一行都表示用户 user1_id 和 user2_id 是朋友。
请注意，user1_id < user2_id。

如果 x  和 y 为 朋友 且他们 至少 有三个共同的朋友 ，那么 x 和 y 之间的友谊就是 坚定的。

写一个解决方案来找到所有的 坚定的友谊。

注意，结果表不应该包含重复的行，并且 user1_id < user2_id。

以 任何顺序 返回结果表。

返回结果格式如下所示。

示例 1:

输入: 
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1        | 2        |
| 1        | 3        |
| 2        | 3        |
| 1        | 4        |
| 2        | 4        |
| 1        | 5        |
| 2        | 5        |
| 1        | 7        |
| 3        | 7        |
| 1        | 6        |
| 3        | 6        |
| 2        | 6        |
+----------+----------+
输出: 
+----------+----------+---------------+
| user1_id | user2_id | common_friend |
+----------+----------+---------------+
| 1        | 2        | 4             |
| 1        | 3        | 3             |
+----------+----------+---------------+
解释: 
用户 1 和 2 有 4 个共同的朋友（3，4，5，和 6）。
用户 1 和 3 有 3 个共同的朋友（2，6，和 7）。
但这里不包括用户 2 和 3 的友谊，因为他们只有两个共同的朋友（1 和 6）。

三、建表语句

drop table if exists friendship;
Create table If Not Exists Friendship (user1_id int, user2_id int);
Truncate table Friendship;
insert into Friendship (user1_id, user2_id) values ('1', '2');
insert into Friendship (user1_id, user2_id) values ('1', '3');
insert into Friendship (user1_id, user2_id) values ('2', '3');
insert into Friendship (user1_id, user2_id) values ('1', '4');
insert into Friendship (user1_id, user2_id) values ('2', '4');
insert into Friendship (user1_id, user2_id) values ('1', '5');
insert into Friendship (user1_id, user2_id) values ('2', '5');
insert into Friendship (user1_id, user2_id) values ('1', '7');
insert into Friendship (user1_id, user2_id) values ('3', '7');
insert into Friendship (user1_id, user2_id) values ('1', '6');
insert into Friendship (user1_id, user2_id) values ('3', '6');
insert into Friendship (user1_id, user2_id) values ('2', '6');

四、题目分析

解题思路：

        1、两人互为好友，是双向的，1是2的好友，2是1的好友，需要把表交叉合并在一起

        2、需求中提到user1_id<user2_id，利用这个条件自关联获得所有的排列组合

        3、筛选相同好友的id和筛选两人是好友见sql解答2.2和2.3步骤

·        4、统计好友数量并筛选大于等于3的

五、SQL解答

六、最终答案

with t1 as (
    -- 1、交叉合并两列
    select user1_id as user_id, user2_id as friend_id from Friendship
    union
    select user2_id,user1_id from friendship
)
select
    a.user_id,
    b.user_id as user2_id,
    count(1) as common_friend
from t1 a
join t1 b
    -- 2.1所有好友的组合
    on a.user_id < b.user_id
    -- 2.2筛选有相同好友
    and a.friend_id = b.friend_id
    -- 2.3有共同好友的两人必须是好友
where (a.user_id,b.user_id) in (select user1_id, user2_id from friendship)
group by a.user_id,b.user_id
-- 2.4筛选共同好友大于等于3的
having count(1) >=3
order by a.user_id,b.user_id

七、验证