mysql - 通过分页根据总和获取用户排名

Question

那里有几个排名帖子，但我还没有看到一个处理结果何时分页以及排名标准(在本例中为:积分)何时等于前一个用户。我已经尝试了一些预先存在的例子，但没有一个奏效。

我有一个名为“users”的表，其中包含“id”列。我还有一个名为“points”的表，其中包含“user_id”和“amount”列。

我需要:

1.) 积分总和重复的用户具有相同的排名

积分表

user_id     amount
   1          10
   2          20
   1           5
   3          20
   3          -5
   4          5

排名应该是

rank     user_id    total
 1          2        20
 2          1        15
 2          3        15
 3          4        5

2.) 需要维护从一个页面到另一个页面的排名，因此排名必须在查询中收集，而不是生成的 PHP。

3.) 显示所有用户，而不仅仅是点表中有行的用户，因为有些用户有 0 点，我想最后显示它们。

现在我只是按照他们的分数顺序列出用户，但没有收集他们的排名，因为它不起作用。

$getfanspoints = mysql_query("SELECT DISTINCT id,
(SELECT SUM(amount) AS points FROM points WHERE points.user_id = users.id) AS points
FROM users
ORDER BY points DESC LIMIT $offset, $fans_limit", $conn);

我已经阅读了这些解决方案，但没有一个有效。

[罗兰的博客][1]

[如何根据SUM得到排名][2]

[MySQL, 获取用户排名][3]

[如何使用mysql查询排名][4]

还有其他一些链接我现在找不到。

有什么建议吗？

[编辑]

我使用了 ypercube 的底部答案。

Answer 1

SELECT COUNT(*) AS rank
     , t.user_id
     , t.total 
FROM
      ( SELECT   user_id
             ,   SUM(amount) AS total
        FROM     points 
        GROUP BY user_id
      ) AS t
  JOIN
      ( SELECT DISTINCT
                 SUM(amount) AS total
        FROM     points 
        GROUP BY user_id
      ) AS dt
    ON 
        t.total <= dt.total
GROUP BY t.user_id
ORDER BY rank
       , user_id

---

但是对于一张大 table 和经常奖励的分数，上面的代码可能真的很慢。拥有这个并在您的应用程序代码中计算排名可能真的更好:

SELECT   users.id    AS user_id
     ,   SUM(amount) AS total
FROM     
         users
  LEFT JOIN
         points
    ON   points.user_id = users.id
GROUP BY users.id
ORDER BY total DESC
       , user_id

---

这也可以工作(经过编辑，可以与 users 表和 OFFSET 一起工作):

SELECT *
FROM
  ( SELECT
          @rank := @rank + (@t <> total) AS rank
        , user_id 
        , @t := total AS total
    FROM 
        ( SELECT   users.id AS user_id
               ,   COALESCE(SUM(amount),0) AS total
          FROM users
            LEFT JOIN points
              ON users.id = points.user_id
          GROUP BY users.id
        ) AS o
      CROSS JOIN
        ( SELECT @rank := 0, @t := -999999
        ) AS dummy
    ORDER BY total DESC
           , user_id
  ) tmp
LIMIT x OFFSET y