在查询结果列表中获取某些结果的索引?

Question

我一直在试图用一种最有意义的方式来写这个标题，但是通过大量的谷歌搜索，我没有找到任何与我正在寻找的匹配的东西。

基本上，我有一个存储玩家和他们不同级别的数据库，我已经有一个工作查询，获取所有玩家的列表，并按照所需的顺序对他们进行排名(首先是总声望，然后是级别，然后是经验，最后是最旧的更新时间戳)

我使用的查询是:

SELECT u.id, u.username, u.mode, u.total_prestige as prestige, u.total_level as level, u.total_xp as exp, s.created FROM hs_users u JOIN hs_userskill s ON u.id = s.userId
WHERE s.id IN (SELECT MAX(id) FROM hs_userskill WHERE userId = u.id GROUP BY userId)
ORDER BY total_prestige DESC, total_level DESC, total_xp DESC, created ASC;

但是现在，在另一个页面上，我需要找到球员的"排名"。(所以基本上他们在结果列表中的索引)是否有一个适当的sql方法这样做(可能)，而不是只是把整个结果集到代码和循环它?就像我现在想做的那样。

我的数据库结构sql:

CREATE TABLE IF NOT EXISTS `hs_modes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`modename` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`modeicon` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `hs_skills` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`skillname` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`skillicon` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `hs_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL UNIQUE,
`mode` int(11) NOT NULL DEFAULT '0',
`total_prestige` int(11) NOT NULL DEFAULT '0',
`total_level` int(11) NOT NULL DEFAULT '0',
`total_xp` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
FOREIGN KEY (`mode`) REFERENCES `hs_modes`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `hs_userskill` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`skillId` int(11) NOT NULL,
`prestige` int(11) NOT NULL,
`experience` int(11) NOT NULL,
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
FOREIGN KEY (`userId`) REFERENCES `hs_users`(`id`),
FOREIGN KEY (`skillId`) REFERENCES `hs_skills`(`id`),
UNIQUE KEY `userskill` (`userId`, `skillId`, `prestige`, `experience`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Answer 1

在mysql 8中，你有ROW_NUMBER来表示在指定顺序的位置

SELECT 
u.id,
u.username,
u.mode,
u.total_prestige AS prestige,
u.total_level AS level,
u.total_xp AS exp,
s.created
,ROW_NUMBER() OVER(ORDER BY total_prestige DESC , total_level DESC , total_xp DESC , created ASC) rn
FROM
hs_users u
JOIN
hs_userskill s ON u.id = s.userId
WHERE
s.id IN (SELECT 
MAX(id)
FROM
hs_userskill
WHERE
userId = u.id
GROUP BY userId)
ORDER BY total_prestige DESC , total_level DESC , total_xp DESC , created ASC;

from Bob rank

SELECT
* 
FROM 
(SELECT 
u.id,
u.username,
u.mode,
u.total_prestige AS prestige,
u.total_level AS level,
u.total_xp AS exp,
s.created
,ROW_NUMBER() OVER(ORDER BY total_prestige DESC , total_level DESC , total_xp DESC , created ASC) rn
FROM
hs_users u
JOIN
hs_userskill s ON u.id = s.userId
WHERE
s.id IN (SELECT 
MAX(id)
FROM
hs_userskill
WHERE
userId = u.id
GROUP BY userId)
) t1
WHERE username = 'Bob'

Answer 2

设置数据库连接游标以字典的形式获取结果，然后可以将数据作为列名的字典索引访问