MySQL 8嵌套选择计数

Question

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，这个查询应该是什么样的呢？

Answer 1

rank这个词在MySql 8中是一个保留字。

因此，使用另一个别名，或回勾别名名称。

在MySql 8中，您可以使用窗口函数

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;

Answer 2

您忽略了outer & subquery中的单引号：

SELECT mapname, 
       (SELECT count(1) FROM ck_bonus b WHERE a.mapname = b.mapname AND a.runtime > b.runtime AND a.zonegroup = b.zonegroup AND b.style = '%i') + 1 AS rnk, 
       (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';

我怀疑您需要LIKE谓词而不是=。