mysql - 选择行计数器未按预期工作的订单

Question

我的排名顺序不正确，在此查询中，顺序与排名不匹配的地方是什么？

SELECT @r := @r+1 as rank, 
        z.* 
 FROM(
         SELECT 
             u.user_id,
             u.score,
             c.username
             FROM `e_highscores` u
     
             JOIN (
                 SELECT  cc.user_id, cc.username
                 FROM e_users as cc
                 ) as c
                 ON  u.user_id = c.user_id
     ORDER BY  u.score DESC
 )z, 
 (SELECT @r:=0)y;

输出:


切换顺序为:

        SELECT @r := @r+1 as rank, 
        z.* 
 FROM(
         SELECT 
             u.user_id,
             u.score,
             c.username
             FROM `e_highscores` u
     
             JOIN (
                 SELECT  cc.user_id, cc.username
                 FROM e_users as cc
                 ) as c
                ON  u.user_id = c.user_id
       
 )z, 
 (SELECT @r:=0)y
  ORDER BY  z.score DESC

产生:

Answer 1

我在我的沙箱(mysql 5.7.31)中创建了一个表，看起来你的查询可以得到正确的结果:

CREATE TABLE `e_highscores` (
  `user_id` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;

CREATE TABLE `e_users` (
  `user_id` int(11) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;

insert into `e_highscores` (user_id, score) values (2, 200), (15, 300), (11,121);
insert into `e_users` (user_id, username) values (2, 'Pie'), (15, 'Adam'), (11, 'Hershey');

SELECT @r := @r+1 as urank,
    z.*
FROM(
 SELECT
     u.user_id,
     u.score,
     c.username
     FROM `e_highscores` u

     JOIN e_users as c
     ON  u.user_id = c.user_id

) z,
(SELECT @r:=0) y
ORDER BY  z.score DESC;

结果是:

1   15  300 Adam
2   2   200 Pie
3   11  121 Hershey

我猜你的问题可能是由mysql中的'rank'关键字引起的？？
你能告诉我你的mysql版本吗？

将用户定义的变量与 order by 一起使用时，mariadb 与 mysql 不同一起。

Beware order of evaluation of rows when sorting. It would appear (atleast as far as I've tested it) that when statements of the form:

SELECT @var:= FROM...

are executed, @var will always correspond to the value in the last rowreturned, as you might expect.

But if you do something like:

SELECT DISTINCT IFNULL(@var:=Name,'unknown') FROM Customers ORDER BY LIMIT 10

The statement containing the variable assignment will be executedbefore the order-by. In this case, the value left in @var at the end might not even correspond to any of the rows returned!

请引用 mariadb offical doc更多细节。
要生成正确的结果，请使用以下查询:

select 
  urank, t.user_id, t.score, c.username 
from (
  select 
    @row := @row + 1 as urank, 
    u.user_id, 
    u.score 
  from e_highscores u 
  order by score desc
) t join e_users as c on t.user_id = c.user_id 
order by score desc;