MySQL 分页排名

Question

我有一个 SQL 查询，如下所示:

SELECT member_id, Count(*) AS '# of Rounds'
FROM   score,cup_point
WHERE  session_id =?
       AND tour_id =?
       AND cup_point_id = `cup_point`.id
GROUP  BY member_id
ORDER  BY Sum(points) DESC
LIMIT  50 offset 0 

如何在查询中包含排名，以便:

1. 我得到一个返回列，其中包含基于分数的数字，即 SUM(points)，因此最高分数的排名为 1，依此类推。
2. 使用偏移量和限制进行分页时，或者即使按成员(member) ID 进行筛选时，我仍然可以获得该成员(member)的正确排名 (member_id)？

谢谢。

Answer 1

也许有一个子查询和一个运行计数器

SET @x = 0;
SELECT member_id,NumRounds AS '# of Rounds',@x:=(@x+1) Rank FROM
(
SELECT member_id, Count(*) AS NumRounds
FROM   score,cup_point
WHERE  session_id =?
       AND tour_id =?
       AND cup_point_id = `cup_point`.id
GROUP  BY member_id
ORDER  BY Sum(points) DESC
LIMIT  50 offset 0 
) A;

I have addressed something like this in the DBA StackExchange on Jun 08, 2012

尝试一下!!!