像使用MySQL一样获得关注者Twitter [英] get followers twitter like using MySQL

问题描述

你好，我说一个例子

我的关注表

A B
1 2 // same so its friend
2 1 // same so its a friend
1 3 // user 1 is following 3
1 4 // user 1 is following 4

等

让我们说我们是用户1我们如何列出我们的朋友? 我的脑袋里有东西

lets say we are the user 1 how can we list our friends ? i have something in my head like

SELECT COUNT(*) FROM social WHERE ((A = B) = (B = A)) as friends
// so it will be something like count friends where ( 1 = 2 ) = ( 1 = 2) if you get my logic

还是我们可以通过某种方式做到这一点?

or can we do that somehow ?

如果有效，将计为1

推荐答案

这应该做到:

SELECT COUNT(me.A) FROM social AS me 
   INNER JOIN social AS you ON me.B = you.A 
WHERE me.A = you.B AND me.A = 1

如果要列出朋友列表，请删除COUNT.

Remove the COUNT if you want a list of friends.

编辑

根据要求，进行解释.

您正在对表进行自身JOIN，因为您对行之间的关系感兴趣.

You're JOINing a table to itself because you're interested in the relationships between rows.

我决定将表别名为me和you，以使关系清楚.也就是说，A列可以将我称为关注者，也可以将您称为关注者. B列指的是跟随者

I decided to alias the tables as me and you to make the relationship clear. What that is saying is that column A can refer to me as the follower or you as the follower. Column B refers to the followee

如果要重命名列，查询将更加清晰

If you were to rename the columns, the query would read more clearly

如果A-> follower和B-> followee，我们将:

if A -> follower and B -> followee, we'd have:

SELECT COUNT(me.follower) FROM social AS me 
   INNER JOIN social AS you ON me.followee = you.follower
WHERE me.follower = you.followee AND me.follower = 1

这就是说，获取此表的两个副本，并复制JOIN行中的行，其中me中的关注者是you中的关注者.然后，通过捕获您对(A == B) && (B == A)

So it's saying, take two copies of this table and JOIN the rows where the followee in me is the follower in you. Then, filter and show only the rows where the follower in me is the followee in you... there by capturing your desire to have (A == B) && (B == A)

也许表别名不是那么好，但是我希望可以澄清一下.

Perhaps the table aliases aren't that great, but I hope that clarifies a little.

第二编辑 根据下面的评论，更清晰的表格可能是:

SECOND EDIT Per comments below, a clearer form may be:

SELECT COUNT(me.A) FROM social AS me 
   INNER JOIN social AS you ON me.A = you.B AND me.B = you.A
WHERE me.A = 1

这篇关于像使用MySQL一样获得关注者Twitter的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！