mysql - "Row_Number() over"语法错误

Question

我有以下表结构

CREATE TABLE `Modular_LS` (
`id` int(11) NOT NULL,
`steamid` varchar(32) NOT NULL,
`name` varchar(32) NOT NULL,
`xp` bigint(20) NOT NULL DEFAULT '0',
`prestige` tinyint(4) NOT NULL DEFAULT '0',
`creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这是我尝试执行的查询:

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'

但我已经明白了

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(ORDER BY `prestige` DESC, `xp` DESC) AS rank, (SELECT COUNT(*) FROM Modular_LS)' at line 1

我做错了什么？这适用于以前安装的 MariaDB，但现在不行了。

我也在使用 MariaDB v10.1.22

Answer 1

MariaDB 团队从 10.2 版及更高版本开始引入了窗口函数(例如 ROW_NUMBER())。

您正在使用 10.1。您之前安装的版本必须是更高版本。