MySQL行位置

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

但我的军衔错了。行号没有正确计算行的位置。也许是关于加入和小组的事？

具有某些ids的表上的多个选择示例：

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

这里的错误是id =3应该在第三位置，而不是在第四位置。而id = 10应该是第1位，而不是第250位，等等。

一个更好的例子：

木琴

Answer 1

这太长了，不能发表评论。样本Fiddle中的问题是排序不稳定。总值为1的有多个值，这些值可以任意排序。而且，他们显然是。

您可以引入一个稳定的排序，方法是在排序中包含一个额外的列来唯一地标识每一行。对于SQL示例，查询如下：

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是这里。)

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