MySQL中row_number()窗口函数HackerRank编译报错本地正常问题咨询

在解决HackerRank的Ollivander's Inventory挑战时，编写了如下SQL语句：

select id,
       age,
       coins_needed,
       power
from(select wands.id as id,
       wands.code as code,
       wands_property.age as age,
       wands.coins_needed as coins_needed,
       wands.power as power,
       wands_property.is_evil as is_evil,
       row_number() over(partition by wands.code,wands.power order by wands.code,wands.power,wands.coins_needed) as rownum
from wands inner join wands_property
on wands.code = wands_property.code
order by wands.code,wands.power) sub
where is_evil = 0 and rownum = 1
order by 4 DESC,2 DESC

该语句在HackerRank的MySQL编译器中报出如下语法错误：

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '(partition by wands.code,wands.power order
by wands.code,wands.power,wands.coins' at line 11

但相同语句在本地MySQL Workbench中可正常运行无报错，请问该问题的根本原因是什么？

问题核心是MySQL版本兼容性差异：

• 本地MySQL Workbench使用的是MySQL 8.0及以上版本，该版本正式支持窗口函数（如row_number() over()这类语法）。
• HackerRank平台的MySQL版本低于8.0（多为5.x系列），此版本不支持窗口函数语法，因此触发语法错误。

可以通过子查询获取每个code+power组合下的最小coins_needed，再关联原表得到对应数据，兼容低版本MySQL：

SELECT w.id, wp.age, w.coins_needed, w.power
FROM wands w
JOIN wands_property wp ON w.code = wp.code
WHERE wp.is_evil = 0
AND w.coins_needed = (
    SELECT MIN(coins_needed)
    FROM wands
    WHERE code = w.code AND power = w.power
)
ORDER BY w.power DESC, wp.age DESC;

内容的提问来源于stack exchange，提问作者Kartike Raj