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

目录 问题：SQL解答：

问题：

现在有一张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 ;