mysql - MySQL 8嵌套带计数的选择

SELECT mapname,
    (SELECT count(1)+1 FROM ck_bonus b WHERE a.mapname=b.mapname AND a.runtime > b.runtime AND a.zonegroup = b.zonegroup AND b.style = %i) AS rank,
    (SELECT count(1) FROM ck_bonus b WHERE a.mapname = b.mapname AND a.zonegroup = b.zonegroup AND b.style = %i) as total
    FROM ck_bonus a WHERE steamid = '%s' AND style = %i;

这段代码以前在mysql8更新之前运行得很好，但现在出现了这个错误

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
 'rank, (SELECT count(1) FROM ck_bonus b WHERE a.mapname = b.mapname AND a.zonegro' at line 1

我浏览了谷歌，找不到正确的答案。
用count（）或count（*）替换count（1）没有帮助。
对于mysql8，这个查询应该是什么样的？

最佳答案

mysql 8中的单词rank是areserved word。
因此，请使用另一个别名，或在别名上打勾。
在mysql 8中，您可以使用window functions

SELECT
 mapname,
 DENSE_RANK() OVER (PARTITION BY mapname, zonegroup, steamid, style ORDER BY runtime DESC) AS `rank`,
 COUNT(*) OVER (PARTITION BY mapname, zonegroup, steamid, style) AS total
FROM ck_bonus
WHERE steamid = '%s' AND style = %i;