MySQL row position

Question

我正在尝试从分组查询中获取行位置。

SELECT x.id, x.position, x.name, x.total FROM (
    SELECT u.*, count(invites.user_id) as total, @rownum := @rownum + 1 AS position 
    FROM users u JOIN (SELECT @rownum := 0) r 
    LEFT JOIN invites i ON i.user_id = u.id AND i.status = 2 
    GROUP BY u.id 
    ORDER BY total DESC
) x
WHERE x.id = 2004

但我的排名出了问题。rownum没有正确计算行的位置。也许是关于加入和小组的事情？

一张表上带有一些id的多选示例：

    id  total   position
    10  15  250
    158 13  124
    3   10  4
    (...)

这里的错误是id＝3应该在第三个位置，而不是在第四个位置。id=10应该是第一个，而不是第250个位置，依此类推。

一个更好的例子：

sqlfiddle

Answer 1

这对于注释来说太长了。示例Fiddle中的问题是排序不稳定。有多个值的总和为1，这些值可以以任意方式排序。而且，他们显然是。

您可以通过在排序中包含一个额外的列来唯一标识每一行，从而引入一个稳定的排序。对于SQL Fiddle示例，查询将是：

SELECT `rank`
FROM (select @rownum:=@rownum+1 `rank`, p.names, count(p.names) as total 
      from TableName p, (SELECT @rownum:=0) r 
      group by names
      order by total, p.names DESC
    ) s
WHERE names = 'ship';
SELECT p.names, count(p.names) as total 
FROM TableName P
GROUP BY names
ORDER BY total desc, p.names;

编辑：

我看到问题了。使用额外的子查询层似乎可以解决这个问题：

select n.*
from (SELECT @rownum:=@rownum+1 as `rank`, n.*
      FROM (select p.names, count(p.names) as total 
            from TableName p cross join (SELECT @rownum:=0) r 
            group by names
           ) n
      order by total desc, names
     ) n
where names = 'mango';

（SQL Fiddle在这里。）

这是一个非常奇怪的行为，我不明白。我想知道这是否与group by之后的隐式排序有关。