mysql - 如何使用 MySQL 查询获得最大的用户推荐？

Question

我创建了一个可以与推荐系统一起使用的新系统，但如何获取推荐次数最多的用户的信息呢？

我想象 SQL 命令是这样的:

select * from user where refered_user=max(count(user.refered_user));

正确的表示法是什么？

Answer 1

我认为您的用户表类似于:

TABLE: user
COLUMN: id int PK
COLUMN: refered_user int FK ON user.id

编辑:删除 HAVING 子句并添加子查询

在不使用 HAVING 查找最大推荐数量的情况下，我添加了一个新的子查询来查找:

SELECT MAX(referals)
FROM (
  SELECT refered_user, COUNT(1) referals
  FROM user
  GROUP BY refered_user
)

然后，我在查询中使用它来获取多次引用的用户:

SELECT refered_user, referals
FROM (
  SELECT refered_user, COUNT(1) referals
  FROM user
  WHERE refered_user IS NOT NULL
  GROUP BY refered_user
) AS ruser
WHERE referals = (
  SELECT MAX(referals)
  FROM (
    SELECT refered_user, COUNT(1) referals
    FROM user
    WHERE refered_user IS NOT NULL
    GROUP BY refered_user
  ) AS muser
)

最后，我使用它作为 JOIN 查询:

SELECT u.*
FROM user u
INNER JOIN (
 SELECT refered_user, referals
 FROM (
  SELECT refered_user, COUNT(1) referals
  FROM user
  WHERE refered_user IS NOT NULL
  GROUP BY refered_user
 ) AS ruser
 WHERE referals = (
  SELECT MAX(referals)
  FROM (
   SELECT refered_user, COUNT(1) referals
   FROM user
   WHERE refered_user IS NOT NULL
   GROUP BY refered_user
  ) AS muser
 )
) AS r ON (r.refered_user = u.id)

结束编辑

请注意，由于多个用户可以拥有相同(最大)数量的推荐，因此此查询可以返回 0(对于空表)、1 行或更多行。