SQL面试题挑战06：互相关注的人

问题：

现在有一张relation表，里面只有两个字段：from_user和to_user，代表关注关系从from指向to，即from_user关注了to_user。现在要找出互相关注的所有人。

from_user    to_user
孙悟空          唐僧
唐僧            如来佛祖
唐僧            观音菩萨
观音菩萨         如来佛祖
唐僧            孙悟空
孙悟空          玉皇大帝
玉皇大帝        如来佛祖
如来佛祖         观音菩萨
如来佛祖         玉皇大帝
如来佛祖         唐僧
孙悟空          猪八戒
猪八戒            嫦娥
猪八戒           孙悟空
猪八戒           唐僧
猪八戒          沙僧
沙僧            猪八戒
沙僧            玉皇大帝
沙僧            孙悟空
沙僧            唐僧

SQL解答：

解答思路一：使用自关联即可，这种方式简单也最易理解。适合数据量不是很大的情况，因为会导致数据膨胀。

with tmp as
(
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
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
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：互相关注 
from tmp a
left join tmp b
on a.from_user=b.to_user and a.to_user=b.from_user
;

解答思路二：找到互相关注的人的规律，当他们是互相关注时，那么将from_user和to_user其中一个顺序调换位置后，from_user和to_user就一定会出现两条数据(源表提前已经去重)，所有出现两条数据的人就是有互相关注的。这种方式不会导致数据膨胀。

with tmp as
(
    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
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
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
(
    select
    from_user
    ,to_user
    --当有互相关注时，保证只将其中的一对用户调换from_user和to_user并拼接
    ,if(from_user>to_user,concat(from_user,to_user),concat(to_user,from_user)) as feature
    from tmp
)t1
;