MySQL 8嵌套带计数的选择 [英] MySQL 8 nested select with count
本文介绍了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更新之前可以正常工作,但现在由于此错误而吐出
This bit of code used to work perfectly fine pre MySQL8 update, but now spits with this error
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
我浏览了google,但找不到正确的答案.用count()或count(*)替换count(1)并没有帮助.
I went through google and I couldn't find the right answer. replacing count(1) with count() or count (*) didn't help.
对于MySQL8,此查询应该是什么样?
What should this query look like for MySQL8?
推荐答案
The word rank
is a reserved word in MySql 8.
因此,请使用另一个别名,或反选别名.
So use another alias name, or backtick the alias name.
在MySql 8中,您可以使用窗口函数
And in MySql 8 you can use 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;
这篇关于MySQL 8嵌套带计数的选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文