我想統計一下用戶之間的共同好友。但是，由于用戶在同一列中，因此我正在努力采用一種方法。
想象一下以下虛擬表：
with users (user_id, user_name)
as (values
(7,' Adam'),
(5,' Tom'),
(35,' Bob'),
(72,' Charlie'),
(2,' Maria'),
(10,' Isabel')
),
friendships (user_id, friend_id)
as ( values
(7, 101),
(7, 102),
(7, 103),
(7, 104),
(7, 105),
(35, 101),
(35, 102),
(35, 103),
(35, 104),
(35, 105),
(7, 201),
(7, 202),
(7, 203),
(2, 201),
(2, 202),
(2, 203),
(7, 301),
(7, 302),
(72, 301),
(72, 302),
(5, 401),
(5, 402),
(5, 403),
(5, 404),
(5, 405),
(5, 406),
(2, 401),
(2, 402),
(2, 403),
(2, 404),
(2, 405),
(2, 406),
(5, 501),
(5, 502),
(5, 503),
(5, 504),
(10, 501),
(10, 502),
(10, 503),
(10, 504),
(5, 601),
(35, 601),
(35, 602),
(35, 603)
)
所以輸出會是這樣的：
id_1 name_1 id_2 name_2 common_friends_count
7 Adam 35 Bob 5
7 Adam 2 Maria 3
7 Adam 72 Charlie 2
5 Tom 2 Maria 6
5 Tom 10 Isabel 4
5 Tom 35 Bob 1
... etc
上面的措辭：亞當和鮑勃有 5 個共同的朋友；亞當和瑪麗亞有 3 個共同的朋友等等......
uj5u.com熱心網友回復：
您需要使用用戶和友誼的兩個不同實體。這很簡單，但如果有不清楚的地方請告訴我，我會添加一個解釋：
with users ...
)
select f1.user_id id_1, u1.user_name name1
, f2.user_id id_2, u2.user_name name2
, count(1)
from friendships f1
join friendships f2
on f1.friend_id = f2.friend_id
and f1.user_id <> f2.user_id
join users u1
on u1.user_id = f1.user_id
join users u2
on u2.user_id = f2.user_id
group by f1.user_id, u1.user_name, f2.user_id, u2.user_name
order by 1,3
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/514688.html