我如何结合这两个查询来计算排名变化？
介绍
我有一个使用等级的游戏的高分表。 分数表格表示当前的高分和玩家信息，最近的表格表示用户最近发布的所有分数，其可能或可能不是新的最高分。
等级下降通过计算玩家的当前等级减去他们在达到他们最新的最高得分时的等级来计算。
等级增加是通过计算他们在达到他们最新的最高分时他们的等级减去他们在达到他们以前的最高分时的等级来计算的。
最后，如代码所示： $change = ($drop > 0 ? -$drop : $increase);
题
我正在使用以下两个查询结合一些PHP代码来计算等级变化。 它工作得很好，但有时会有点慢。
有没有办法来优化或组合两个查询+ PHP代码？
我创建了第一个查询的SQL小提琴：http://sqlfiddle.com/#!9/30848/1
这些表格已经充满了内容，所以它们的结构不应该改变。
这是当前的工作代码：
$q = "
select
(
select
coalesce(
(
select count(distinct b.username)
from recent b
where
b.istopscore = 1 AND
(
(
b.score > a.score AND
b.time <= a.time
) OR
(
b.score = a.score AND
b.username != a.username AND
b.time < a.time
)
)
), 0) + 1 Rank
from scores a
where a.nickname = ?) as Rank,
t.time,
t.username,
t.score
from
scores t
WHERE t.nickname = ?
";
$r_time = 0;
if( $stmt = $mysqli->prepare( $q ) )
{
$stmt->bind_param( 'ss', $nick, $nick );
$stmt->execute();
$stmt->store_result();
$stmt->bind_result( $r_rank, $r_time, $r_username, $r_score );
$stmt->fetch();
if( intval($r_rank) > 99999 )
$r_rank = 99999;
$stmt->close();
}
// Previous Rank
$r_prevrank = -1;
if( $r_rank > -1 )
{
$q = "
select
coalesce(
(
select count(distinct b.username)
from recent b
where
b.istopscore = 1 AND
(
(
b.score > a.score AND
b.time <= a.time
) OR
(
b.score = a.score AND
b.username != a.username AND
b.time < a.time
)
)
), 0) + 1 Rank
from recent a
where a.username = ? and a.time < ? and a.score < ?
order by score desc limit 1";
if( $stmt = $mysqli->prepare( $q ) )
{
$time_minus_one = ( $r_time - 1 );
$stmt->bind_param( 'sii', $r_username, $time_minus_one, $r_score );
$stmt->execute();
$stmt->store_result();
$stmt->bind_result( $r_prevrank );
$stmt->fetch();
if( intval($r_prevrank) > 99999 )
$r_prevrank = 99999;
$stmt->close();
}
$drop = ($current_rank - $r_rank);
$drop = ($drop > 0 ? $drop : 0 );
$increase = $r_prevrank - $r_rank;
$increase = ($increase > 0 ? $increase : 0 );
//$change = $increase - $drop;
$change = ($drop > 0 ? -$drop : $increase);
}
return $change;
如果您将当前最高分分为一个新表，而所有原始数据都在最近的分数中可用，那么您已经有效地生成了一个汇总表。
为什么不继续总结和总结您需要的所有数据？
这只是一个你知道什么，什么时候知道它的例子：
我会改变你的分数表，以包括两个新的列：
并在更新/插入每一行时调整这些列。 看起来您已经有了可用于为第一次迭代备份这些数据的查询。
现在你的查询变得更简单了
从分数获得排名：
SELECT COUNT(*) + 1 rank
FROM scores
WHERE score > :score
从用户名：
SELECT COUNT(*) + 1 rank
FROM scores s1
JOIN scores s2
ON s2.score > s1.score
WHERE s1.username = :username
等级变化成为：
$drop = max($current_rank - $rank_on_update, 0);
$increase = max($old_rank_on_update - $rank_on_update, 0);
$change = $drop ? -$drop : $increase;
UPDATE
如果你坚持按时间分开，如果你还没有更新行，这将适用于新行：
SELECT COUNT(*) + 1 rank
FROM scores
WHERE score >= :score
另一个查询将变为：
SELECT COUNT(*) + 1 rank
FROM scores s1
JOIN scores s2
ON s2.score > s1.score
OR (s2.score = s1.score AND s2.time < s1.time)
WHERE s1.username = :username
但我至少会尝试工会的表现：
SELECT SUM(count) + 1 rank
FROM (
SELECT COUNT(*) count
FROM scores s1
JOIN scores s2
ON s2.score > s1.score
WHERE s1.username = :username
UNION ALL
SELECT COUNT(*) count
FROM scores s1
JOIN scores s2
ON s2.score = s1.score
AND s2.time < s1.time
WHERE s1.username = :username
) counts
关于(score, time)的索引在这里会有所帮助。
就我个人而言，我会让自己头痛，并保持相同的分数（相当标准，我相信）。如果你希望人们能够声称第一吹牛的权利，只要确保你在任何得分图表上按时间排序，并包括显示时间。
我花了很多时间试图弄清楚排名逻辑是什么，并提出评论。 与此同时，这里是一个连接查询，你可以在你的数据上运行 - 我认为你的解决方案会有这样的效果：
SELECT s.username, count(*) rank
FROM scores s LEFT JOIN recent r ON s.username != r.username
WHERE r.istopscore
AND r.score >= s.score
AND r.time <= s.time
AND (r.score-s.score + s.time-r.time)
GROUP BY s.username
ORDER BY rank ASC;
+----------+------+
| username | rank |
+----------+------+
| Beta | 1 |
| Alpha | 2 |
| Echo | 3 |
+----------+------+
（请注意，最后的AND只是为了确保你不会考虑r.score == s.score && r.time == s.time - 我想这将是一个“领带”游戏？）
我不是MySQL的人，但我认为在任何RDBMS中使用自连接进行排名是一个不好的做法。 您应该考虑使用排名功能。 但是MySQL中没有排名功能。 但是有一些解决方法。
链接地址: http://www.djcxy.com/p/31925.html上一篇: How do I combine these two queries to calculate rank change?