問題：

現(xiàn)在有一張relation表，里面只有兩個(gè)字段：from_user和to_user，代表關(guān)注關(guān)系從from指向to，即from_user關(guān)注了to_user?，F(xiàn)在要找出互相關(guān)注的所有人。

from_user    to_user
孫悟空          唐僧
唐僧            如來(lái)佛祖
唐僧            觀音菩薩
觀音菩薩         如來(lái)佛祖
唐僧            孫悟空
孫悟空          玉皇大帝
玉皇大帝        如來(lái)佛祖
如來(lái)佛祖         觀音菩薩
如來(lái)佛祖         玉皇大帝
如來(lái)佛祖         唐僧
孫悟空          豬八戒
豬八戒            嫦娥
豬八戒           孫悟空
豬八戒           唐僧
豬八戒          沙僧
沙僧            豬八戒
沙僧            玉皇大帝
沙僧            孫悟空
沙僧            唐僧

SQL解答：

解答思路一：使用自關(guān)聯(lián)即可，這種方式簡(jiǎn)單也最易理解。適合數(shù)據(jù)量不是很大的情況，因?yàn)闀?huì)導(dǎo)致數(shù)據(jù)膨脹。

with tmp as
(
select '孫悟空' as from_user ,     '唐僧'   as to_user
union all
select '唐僧' as from_user ,     '如來(lái)佛祖'   as to_user
union all
select '唐僧' as from_user ,     '觀音菩薩'   as to_user
union all
select '觀音菩薩' as from_user ,     '如來(lái)佛祖'   as to_user
union all
select '唐僧' as from_user ,     '孫悟空'   as to_user
union all
select '孫悟空' as from_user ,     '玉皇大帝'   as to_user
union all
select '玉皇大帝' as from_user ,     '如來(lái)佛祖'   as to_user
union all
select '如來(lái)佛祖' as from_user ,     '觀音菩薩'   as to_user
union all
select '如來(lái)佛祖' as from_user ,     '玉皇大帝'   as to_user
union all
select '如來(lái)佛祖' as from_user ,     '唐僧'   as to_user
union all
select '孫悟空' as from_user ,     '豬八戒'   as to_user
union all
select '豬八戒' as from_user ,     '嫦娥'   as to_user
union all
select '豬八戒' as from_user ,     '孫悟空'   as to_user
union all
select '豬八戒' as from_user ,     '唐僧'   as to_user
union all
select '豬八戒' as from_user ,     '沙僧'   as to_user
union all
select '沙僧' as from_user ,     '豬八戒'   as to_user
union all
select '沙僧' as from_user ,     '玉皇大帝'   as to_user
union all
select '沙僧' as from_user ,     '孫悟空'   as to_user
union all
select '沙僧' as from_user ,     '唐僧'   as to_user
)
select
a.from_user,
a.to_user,
if(b.from_user is not null, 1, 0) as is_friend -- 1：互相關(guān)注 
from tmp a
left join tmp b
on a.from_user=b.to_user and a.to_user=b.from_user
;

解答思路二：找到互相關(guān)注的人的規(guī)律，當(dāng)他們是互相關(guān)注時(shí)，那么將from_user和to_user其中一個(gè)順序調(diào)換位置后，from_user和to_user就一定會(huì)出現(xiàn)兩條數(shù)據(jù)(源表提前已經(jīng)去重)，所有出現(xiàn)兩條數(shù)據(jù)的人就是有互相關(guān)注的。這種方式不會(huì)導(dǎo)致數(shù)據(jù)膨脹。

with tmp as
(select '孫悟空' as from_user ,     '唐僧'   as to_user
union all
select '唐僧' as from_user ,     '如來(lái)佛祖'   as to_user
union all
select '唐僧' as from_user ,     '觀音菩薩'   as to_user
union all
select '觀音菩薩' as from_user ,     '如來(lái)佛祖'   as to_user
union all
select '唐僧' as from_user ,     '孫悟空'   as to_user
union all
select '孫悟空' as from_user ,     '玉皇大帝'   as to_user
union all
select '玉皇大帝' as from_user ,     '如來(lái)佛祖'   as to_user
union all
select '如來(lái)佛祖' as from_user ,     '觀音菩薩'   as to_user
union all
select '如來(lái)佛祖' as from_user ,     '玉皇大帝'   as to_user
union all
select '如來(lái)佛祖' as from_user ,     '唐僧'   as to_user
union all
select '孫悟空' as from_user ,     '豬八戒'   as to_user
union all
select '豬八戒' as from_user ,     '嫦娥'   as to_user
union all
select '豬八戒' as from_user ,     '孫悟空'   as to_user
union all
select '豬八戒' as from_user ,     '唐僧'   as to_user
union all
select '豬八戒' as from_user ,     '沙僧'   as to_user
union all
select '沙僧' as from_user ,     '豬八戒'   as to_user
union all
select '沙僧' as from_user ,     '玉皇大帝'   as to_user
union all
select '沙僧' as from_user ,     '孫悟空'   as to_user
union all
select '沙僧' as from_user ,     '唐僧'   as to_user
)
select
from_user
,to_user
,count(1) over(partition by feature) as is_friend ---1：不是 2：是
from
(selectfrom_user,to_user--當(dāng)有互相關(guān)注時(shí)，保證只將其中的一對(duì)用戶調(diào)換from_user和to_user并拼接,if(from_user>to_user,concat(from_user,to_user),concat(to_user,from_user)) as featurefrom tmp
)t1
;