具有 3 个分离度级别的表 [英] Table with 3 levels of degree of separation
问题描述
我正在寻找一个查询来在 MySQL 中创建一个具有两个用户之间分离程度的表.我已经找到了分离度查询.但是,如果我理解正确,这将导致与共同朋友推荐的好友列表.我要找的东西略有不同.
我有一个包含用户之间朋友"的表格(不包含 1 到 2 和 2 到 1 之类的重复关系).
<块引用>friends
(id
, initiator_user_id
, friend_user_id
, is_confirmed
)>
我正在尝试创建一个表格,其中包含朋友、朋友的朋友和 FoFoF 之间的所有关系.像这样:
<块引用>relation_degrees
(id
, first_user_id
, second_user_id
, relation_degree
)>
因此relation_degree 列仅包含值1(朋友)、2(FoF)和3(FoFoF).
我能够在 Excel 中完成它,但是我的朋友们存储在一个矩阵中,这使得 IMO 的计算更容易一些.我希望有人能够给我一个提示,让我在 MySQL 中做同样的事情.
谢谢!!
<小时>在 Fluffeh 的帮助下,我找到了以下解决我的问题的方法.
- 我将两个方向的关系(如 1-2 & 2-1,所以没有确认列)存储在名为 degree_one 的表中
- 然后我使用了来自 fluffeh 的第一级和第二级查询来制作带有 first & 的表.二级关系.我添加了一个 WHERE 用户 <> Friend 语句来过滤关系(我想这是来自 fluffeh 的三级关系查询无法正常工作的原因之一)
<块引用>
`创建表 degree_two选择mb.user 作为用户,mb.friend 作为朋友,min(mb.rel) 作为relation_degree
来自(选择1 作为相对,fr1.用户,fr1.朋友从degree_one fr1
联合所有选择2 作为相对,fr2.用户,fr3.朋友从degree_one fr2左外连接 degree_one fr3在 fr2.Friend=fr3.User) mb哪里用户 <>朋友通过...分组mb.用户,mb.朋友
- 然后我用这个表做了几乎相同的查询.select 语句在 degree_two 表上,但外连接仍然来自 degree_one 表.
<块引用>
创建表 degree_three选择mb.user 作为用户,mb.friend 作为朋友,min(mb.relation_degree) 作为relation_degree从(选择fr1.relation_degree,fr1.用户,fr1.朋友从degree_two fr1联合所有选择3 作为相对,fr2.用户,fr3.朋友从degree_two fr2左外连接 degree_one fr3在 fr2.Friend=fr3.User) mb其中`用户` <>`朋友`通过...分组mb.用户,mb.朋友
这是一种解决方法,但它为我提供了所需的输出.我仍然想知道为什么来自 fluffeh 的查询不能正常工作,因为我真的想要一个单一的查询作为解决方案.我将继续玩弄查询...我希望有人可以帮助我将这些查询合并为一个.
这个查询给了我想要的解决方案结果:
创建表 degree_two选择mb.user 作为用户,mb.friend 作为朋友,min(mb.rel) 作为relation_degree从(选择1 作为相对,fr1.用户,fr1.朋友从degree_one fr1联合所有选择2 作为相对,fr2.用户,fr3.朋友从degree_one fr2左外连接 degree_one fr3在 fr2.Friend=fr3.User其中 fr2.user <>fr3.朋友联合所有选择3 作为相对,fr4.user,fr6.朋友从(degree_one fr4左外连接 degree_one fr5在 fr4.friend=fr5.user和 fr4.user <>fr5.朋友左外连接 degree_one fr6在 fr5.friend = fr6.user和 fr5.user <>fr6.朋友)其中 fr6.friend 不是 NULL) mb通过...分组mb.用户,mb.朋友
感谢 Fluffeh 的帮助!
I'm looking for a query to create a table in MySQL with the degree of seperation between two users. I already found Degrees of Separation Query.But that will, if i understand correctly, result in a recommended friendlist with mutual friends. What i'm looking for is slightly different.
I have a table with "friends" between users (contains no duplicate relations like 1 to 2 & 2 to 1).
friends
(id
,initiator_user_id
,friend_user_id
,is_confirmed
)
What i am trying to create is a table with all relations between friends, friends of friends and FoFoF. like this:
relation_degrees
(id
,first_user_id
,second_user_id
,relation_degree
)
so the relation_degree column only contains the value 1 (friends), 2 (FoF) and 3 (FoFoF).
I was able to do it in Excel, but there my friends where stored in a matrix, which make calculations IMO a little bit easier. I hope somebody will be able to give me a hint to do the same in MySQL.
Thanks!!
edit: with the help from Fluffeh i found the following solution to my problem.
- i stored the relations in both directions( like 1-2 & 2-1, so without the confirmation column) in table called degree_one
- Then i used the query for degree one and two from fluffeh to make table with first & second degree relations. I added a WHERE user <> Friend statement to filter the relations (i guess this is one of the reasons why the query from fluffeh for the third degree relations isn't working correctly)
`Create table degree_two select mb.user as User, mb.friend as Friend, min(mb.rel) as relation_degree
from ( select 1 as rel, fr1.User, fr1.Friend from degree_one fr1
union all select 2 as rel, fr2.User, fr3.Friend from degree_one fr2 left outer join degree_one fr3 on fr2.Friend=fr3.User ) mb Where user <> friend group by mb.User, mb.Friend
- Then i used this table to do almost the same query. the select statement is on the degree_two table but the outer join is still from the degree_one table.
Create table degree_three select mb.user as User, mb.friend as Friend, min(mb.relation_degree) as relation_degree from ( select fr1.relation_degree, fr1.User, fr1.Friend from degree_two fr1 union all select 3 as rel, fr2.User, fr3.Friend from degree_two fr2 left outer join degree_one fr3 on fr2.Friend=fr3.User ) mb Where `user` <> `friend` group by mb.User, mb.Friend
It is kind of a work around but it gives me the desired output. I'm still wondering why the query from fluffeh doesn't work correctly, because i really want a single query as solution. i will continue fooling around with the query... I hope that somebody can help me merging these query into one.
This query gave me the desired solution result:
Create table degree_two
select
mb.user as User,
mb.friend as Friend,
min(mb.rel) as relation_degree
from
(
select
1 as rel,
fr1.User,
fr1.Friend
from
degree_one fr1
union all
select
2 as rel,
fr2.User,
fr3.Friend
from
degree_one fr2
left outer join degree_one fr3
on fr2.Friend=fr3.User
where fr2.user <> fr3.friend
union all
select
3 as rel,
fr4.user,
fr6.Friend
from
(degree_one fr4
left outer join degree_one fr5
on fr4.friend=fr5.user
and fr4.user <> fr5.friend
left outer join degree_one fr6
on fr5.friend = fr6.user
and fr5.user <> fr6.friend)
where fr6.friend IS NOT NULL
) mb
group by
mb.User,
mb.Friend
Thanks Fluffeh for your help!
这篇关于具有 3 个分离度级别的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!