mysql - MySQL 5.6.39 中的等效语句是什么？

Question

我最近搬到了一台具有 MySQL 5.6.39 而不是 MariaDB 10.x 的共享主机，我想知道 MySQL 中的以下 MariaDB 语句相当于什么？

SELECT rank,
       total
FROM
  (SELECT ROW_NUMBER() OVER (
                             ORDER BY `prestige` DESC, `xp` DESC) AS rank,

     (SELECT COUNT(*)
      FROM Modular_LS) AS total,
                            steamid
   FROM Modular_LS) sub
WHERE sub.steamid = '%s'

我已经做到了这一点，但现在我陷入困境

SELECT rank, total FROM
                        (SELECT @rank := @rank +1 as rank FROM Modular_LS,
                        (SELECT COUNT(*) FROM Modular_LS) AS total, steamid FROM Modular_LS) sub,
                        (SELECT @rank := 0) r ORDER BY `prestige` DESC, `xp` DESC) t;

表结构包含列 steamid、xp、prestige

我的目标是先按声望降序排列，然后按经验值降序排列，将其放入类似排名的顺序，然后使用 WHERE 查询查找特定玩家的排名。其输出包含rank(位置)和total(记录总数)

Answer 1

也许这会让您开始:

SELECT  @rank := IF(player_id = @prev, @rank + 1, 1), @prev := player_id
    FROM ( SELECT  @rank := 1, @prev = 0 ) AS init
    JOIN ( SELECT  player_id
               FROM  Modular_LS
               ORDER BY  prestige DESC, SP DESC 
         ) AS x ;