mysql - 获取有关注者和无关注者的用户

Question

我有一个非常简单的表 - follow - 我在其中存储关注者。

user | following
-----------------
1    | 2

以上表示用户1正在关注用户2。

我想在主页上显示所有用户，并命令他们购买拥有最多关注者的用户，然后返回其余没有关注者的用户。下面的查询可以显示用户，但我不知道如何检索没有任何关注者的用户。我尝试过 RIGHT JOIN users u ON f.following=u.id 但这给了我奇怪的结果。

此查询返回有关注者的用户 2，但不会返回没有关注者的用户 1 和 3。

编辑:此查询还会检查用户是否回访，这就是我使用 ID 1 加入作为测试的原因。

SELECT 
     u.id
    ,u.username
    ,u.avatar
    ,COUNT(1) AS followers
    ,ul.*
    ,fo.*
FROM  follow f
LEFT JOIN users u ON f.following=u.id
LEFT JOIN follow fo ON fo.following=u.id AND fo.user=1 
LEFT JOIN users_likes ul ON ul.likes=u.id AND ul.user=1 
GROUP BY f.following
ORDER BY COUNT(1) DESC

SQL fiddle :http://sqlfiddle.com/#!2/98f65/1

Answer 1

问题中查询的问题是您左连接下表。这意味着将包含下表中的所有行，无论它们与另一个表的连接如何。您想要的是显示所有用户，因此该表应该位于联接的外端。

我还认为您试图在这里同时做太多事情，这就是您难以弄清楚的原因。您想知道谁有关注者，谁没有，谁在关注，对他们进行排序，考虑用户的喜好等等。我建议退后一步，将它们分解为单独的查询，然后根据需要将它们构建到一个结果集中。

要获取用户和关注者数量，您可以将用户表与以下表进行外部联接，如下所示:

SELECT u.id, u.username, u.avatar, (IFNULL(COUNT(f.following), 0)) AS numFollowers
FROM users u
LEFT JOIN follow f ON f.following = u.id
GROUP BY u.id
ORDER BY numfollowers DESC;

IFNULL 用于检查没有关注者的情况，并且外连接中没有进行链接，因此出现空值。

如果您想在 users_likes 表中工作，您应该将其添加为另一个左联接。这导致的问题是，如果没有喜欢，它将为所有列返回空值。 (例如，如果我在此处保留加入 users_likes 表，我将看到用户 1 和 3 为 null，因为没有人“喜欢”他们。)为了使结果集更容易理解，我建议您不要收集用户 1 和 3 的所有行。 users_likes 表。也许这个查询更有意义:

SELECT u.id, u.username, u.avatar, (IFNULL(COUNT(f.following), 0)) AS numFollowers, ul.user AS likedByUser, ul.created_at
FROM users u
LEFT JOIN follow f ON f.following = u.id
LEFT JOIN users_likes ul ON ul.likes = u.id
GROUP BY u.id
ORDER BY numfollowers DESC;

至于用户是否回关注，我认为这会有所改变，因为上面仅显示关注者的数量，并且不会为每个关注者生成一行。

如果您还有任何疑问，请告诉我，这是 SQL Fiddle对于上述情况。我将让您自行处理现在出现的空值。