mysql - 推荐好友积分查询

Question

我正在尝试找出实现此目标的最佳方法，非常感谢任何意见。

我的 MYSQL 表的一部分:

ID , Username , Invited_by
1  , A        , 
2  , B        , 1
3  , C        , 2
4  , D        , 2
5  , E        , 4
6  , F        , 5

所以我想弄清楚谁邀请了最多的用户，技巧部分是如果 A 邀请了 B 并且 B 邀请了 C 那么我将计算 A 邀请了 2，我想要实现的是这个。

ID , Username , Invited
1  , A        , 5
2  , B        , 4
3  , C        , 0
4  , D        , 2
5  , E        , 1
6  , F        , 0

解释

• F 没有邀请任何人，C 也一样
• E邀请了F所以他得到1分
• D 邀请了 E 所以这意味着 E 注册后他邀请了 F 所以 D 得到 2 分
• B邀请了C和D，D又邀请了2个人所以B得4分
• A 邀请了 B，他邀请了 4 人，所以 A 有 5 个人

我知道这很复杂，这就是为什么我试图找出最佳解决方案的原因。

谢谢，

更新

所以在我尝试了不同的方法之后，我相信我想出的最好的方法如下:

• 例如将 1 个字段作为“total_invites”添加到用户表
• 构建一个查询来计算每个用户的邀请数量，从最新用户开始直到老用户，因为新用户可能还没有机会邀请任何人。
• 在数据库上运行查询“cronjob 或每次在有限的行上”
• 一旦我达到平衡表，逻辑就会改变，而不是计算结果，一旦我需要报告，我就会在新推荐用户登录时增加每个帐户和相关帐户的计数器。

注意事项

• 即使为单个用户构建查询看起来也很复杂，我认为我必须进行 n 次查询，直到到达该用户的邀请树底部
• 我想一旦 table 达到平衡状态，逻辑就会变得容易得多。

如果您有任何可能有帮助的想法或引用资料，我将不胜感激。

Answer 1

我不知道这是否是您要找的东西，但是如果有很多左连接，它会有点长。

 select t1.ID , t1.Username , count(t2.ID) + count(t3.ID) +  count(t4.ID)  as Invited 
 from table1 t1
 left join table1 t2 On t1.ID = t2.Invited_by
 left join table1 t3 On t2.ID = t3.Invited_by
 left join table1 t4 On t3.ID = t4.Invited_by
 group by t1.ID

DEMO HERE

输出:

 ID     USERNAME    INVITED
  1       A            5
  2       B            4
  3       C            0
  4       D            2
  5       E            1
  6       F            0

如果你很关心好友列表，那么检查这个函数程序来循环抛出他们。 get a recursive parent list

编辑 2:

对于我的想法来说是一个很好的方法，当然，如果你愿意的话，这取决于你。

不是插入以前被邀请的人的 Id，你可以连接所有以前被邀请的人。就这样

    (1, 'A', NULL),
    (2, 'B', 'A'),
    (3, 'C', 'A,B'),
    (4, 'D', 'A,B'),
    (5, 'E', 'A,B,D'),
    (6, 'F', 'A,B,D,E')

每次插入一个被邀请的人时，都要将以前的值与新邀请的人连接起来。你已经循环了它们。而且您不需要使用很多查询，只需一个。

看看this DEMO