英文:
Method to get the placement of a player with MySQL Query
问题
我在一个SQL查询中遇到了问题。我想要开发一个Minecraft插件，插件包含一个统计功能，我想要在这个统计数据中创建一个排行榜。击杀数最多的玩家排名第一，依此类推。我创建了一个方法来计算你的排名。首先，SQL查询对表进行排序，然后查找你的位置。
PreparedStatement ps = MySQL.getConnection().prepareStatement("SELECT * FROM (SELECT @r := @r+1 as pos, kills. * FROM kills, (SELECT @r := 1) tmp ORDER BY Kills DESC) WHERE UUID = ?");
ps.setString(1, uuid.toString());
ResultSet rs = ps.executeQuery();
错误信息是：
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'WHERE UUID = '37bb2c2c-e170-469c-a08e-6a22e7d083cd'' at line 1
我不知道这个错误是什么。
英文:
I`ve a Problem with a SQL Query. I want to develop a Minecraft Plugin. The Plugin contains a Statistic. I want to create a Ranking in the Statistic. The player who have the most kills is on the first place and so on.. I created a Method which calculate your placement. First the SQL Query sort the tabel and than it looks in which your you are.
PreparedStatement ps = MySQL.getConnection().prepareStatement("SELECT * FROM (SELECT @r := @r+1 as pos, kills. * FROM kills, (SELECT @r := 1) tmp ORDER BY Kills DESC) WHERE UUID = ?");
ps.setString(1, uuid.toString());
ResultSet rs = ps.executeQuery();
The Error is:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'WHERE UUID = '37bb2c2c-e170-469c-a08e-6a22e7d083cd'' at line 1
I dont know what the Error is.
答案1
得分: 0
你不能在JDBC MySQL查询中使用用户变量。以下是查询的一种解决方法：
SELECT *,
1 + (SELECT COUNT(*) FROM kills k2 WHERE k2.Kills > k1.Kills) AS pos
FROM kills k1
WHERE UUID = ?
如果你使用的是MySQL 8+，那么分析函数是更好的方法：
SELECT *, ROW_NUMBER() OVER (ORDER BY Kills DESC) pos
FROM kills
WHERE UUID = ?
你可能会考虑使用RANK或者DENSE_RANK。需要注意，第一种相关子查询的方法在有两个或更多记录的击杀数相同时，在技术上返回的是一个_排名（rank）_，而不是行号（row number）。
英文:
You can't use user variables in a JDBC MySQL query. Here is one workaround for the query:
SELECT *,
1 + (SELECT COUNT(*) FROM kills k2 WHERE k2.Kills > k1.Kills) AS pos
FROM kills k1
WHERE UUID = ?
If you are using MySQL 8+, then analytic functions are a better way to go:
SELECT *, ROW_NUMBER() OVER (ORDER BY Kills DESC) pos
FROM kills
WHERE UUID = ?
You may want to instead use RANK or DENSE_RANK. Note that the first correlated subquery approach is technically returning a rank, not a row number, in the event that two or more records be tied for the number of kills.
专注分享java语言的经验与见解，让所有开发者获益！
评论