【LC中等】1949. 坚定的友谊

❤️博客主页: 楚生辉
❤️系列专栏:【LeetCode刷题】
❤️一句短话: 坚持不懈，孜孜不倦

1.题目描述

表名: Friendship

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

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

写一个 SQL 查询来找到所有的坚定的友谊。

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

以任何顺序返回结果表。

查询结果的格式在下面的例子中。

示例 1:

输入: 
表 Friendship:
+----------+----------+
| 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）。

2.代码实现

一开始的思路是只使用了一层JOIN连接，筛选出共同朋友的，也即user2_id相同的

1 3 2 3
1 4 2 4
1 5 2 5
1 6 2 6

直接就可以得出共同的朋友，后来发现这样做其实是不对的，比如1的朋友是a，2的朋友也是a，但是我们不能得出1与2的共同朋友是a，因为1与2的关系并不确定，因此还需要再加一层JOIN连接来确定这一层关系

WITH a AS ( SELECT * FROM friendship UNION SELECT user2_id, user1_id FROM friendship ) 

SELECT
	f.user1_id,
	f.user2_id,
	count(*) AS common_friend
FROM
	friendship f
	JOIN a a1 ON f.user1_id = a1.user1_id
	JOIN a a2 ON f.user2_id = a2.user1_id 
	AND a2.user2_id = a1.user2_id
GROUP BY
	f.user1_id,
	f.user2_id 
HAVING
	count(*)>= 3