苏州网站开发公司招聘信息,电子商务网站建设学什么,wordpress图片要求,大型企业网站欣赏目录 问题#xff1a;SQL解答#xff1a; 问题#xff1a;
现在有一张relation表#xff0c;里面只有两个字段#xff1a;from_user和to_user#xff0c;代表关注关系从from指向to#xff0c;即from_user关注了to_user。现在要找出互相关注的所有人。 from_user to_… 目录 问题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_userb.to_user and a.to_userb.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
(selectfrom_user,to_user--当有互相关注时保证只将其中的一对用户调换from_user和to_user并拼接,if(from_userto_user,concat(from_user,to_user),concat(to_user,from_user)) as featurefrom tmp
)t1
;
