我不是一个优秀的php程序员（我来自C++）。我只将php用于数据库输入。
我有一个包含以下内容的数据库：
UserId (an unique int)
AsyncPointsAverage (float)
AsyncPointsAverageRank (a position based on the value immediately above)
AsyncPointsRecentAverage (float an average for the last 5 tests only)
AsyncPointsRecentAverageRank (a position based on the value immediately above)
该表中大约有1000-1500个条目。每天早上和下午，有5个人参加一项测试，影响他们的总体平均水平和最近的平均水平。（这在其他地方更新，但这里没有显示。）计算完这5个人的排名后，所有1000-1500人的排名都会受到影响，所以我写了下面的代码。它是最优的吗？
我最关心的是，我正在做大约1000次MySql更新。这很棒吗？我应该换一种方式吗？（也可以随意优化函数中的任何其他代码。正如我所说，我来自C++背景，所以不知道php的细微差别。）
// Sorts by array entry 1
function ReRankCompareAverage($a, $b)
{
if($a[1] == $b[1]) return 0;
else return ($a[1] > $b[1] ? 1 : -1);
}
// Sorts by array entry 2
function ReRankCompareAverageRecent($a, $b)
{
if($a[2] == $b[2]) return 0;
else return ($a[2] > $b[2] ? 1 : -1);
}
function ReRank($db)
{
$i = 0, $j = 0;
$usersARR = null;
$stmt = $db->prepare("SELECT UserId, AsyncPointsAverage, AsyncPointsRecentAverage FROM studenttable");
$stmt->execute();
if($stmt && isset($stmt) && $stmt->rowCount() > 0)
{
$i = 0;
while(($row = $stmt->fetch(PDO::FETCH_ASSOC)))
{
$usersARR[$i][0] = intval($row['UserId']);
$usersARR[$i][1] = floatval($row['AsyncPointsAverage']);
$usersARR[$i][2] = floatval($row['AsyncPointsRecentAverage']);
$i++;
}
}
$stmt->closeCursor(); // mysql_free_result equivalent
// The first pass of $j == 3 does the ranking by Average, filling position $usersARR[][3] with that rank
// The second pass of $j == 4 does the ranking by AverageRecent, filling position $usersARR[][4] with that rank
for($j = 3, $j <= 4; $j++)
{
$iCompare = $j == 3 ? 1 : 2;
usort($usersARR, $j == 3 ? "ReRankCompareAverage" : "ReRankCompareAverageLast");
$count = count($usersARR);
if($count > 0)
{
// Start it off, with the person with the highest average is rank 1
$usersARR[$count - 1][$j] = 1; // Position $j is filled with the rank
// Now loop starting from the second one down
for($i = $count - 2, $rank = 1; $i >= 0; $i--)
{
// Only change the rank if the next one down is strictly lower than the one above, otherwise will share the same rank
if($usersARR[$i][$iCompare] < $usersARR[$i+1][$iCompare]) $rank = $count - $i; // Otherwise keep the same rank, because they are equal
$usersARR[$count - 1][$j] = $rank;
}
}
}
// Now $usersARR is filled with the correct rankings, and they are asscoiated with $UserId
// Now we must put all of these rankings into the database
$count = count($usersARR);
for($i = 0; $i < $count; $i++)
{
$stmt = $db->prepare("UPDATE studenttable SET AsyncPointsAverageRank=:AsyncPointsAverageRank, AsyncPointsRecentAverageRank=:AsyncPointsRecentAverageRank "
. "WHERE UserId=:UserId");
$stmt->execute(array(':AsyncPointsAverageRank' => $usersARR[$i][3],
':AsyncPointsRecentAverageRank' => $usersARR[$i][4],
':UserId' => $usersARR[$i][0]));
}
}
您需要如何使用排名？也许你不需要储存等级？它们可能很容易计算：
SELECT COUNT(*)
FROM studenttable
WHERE AsyncPointsAverage > $currentUserVariableAsyncPoints
显示前10名：
SELECT * FROM studenttable ORDER BY AsyncPointsAverage DESC LIMIT 0,10
等等。
编辑：
要用位置号显示完整的排名，您可以在PHP中进行（您已经在循环中获得了它，在循环中获取行只显示$i++变量）。或者你可以尝试纯SQL（就我个人而言，我更喜欢它）：
SET @rank=0; SELECT @rank := @rank +1 AS rank, UserId, AsyncPointsAverage
FROM studenttable
ORDER BY AsyncPointsAverage DESC
为了扩展Mark的答案，您不需要每次添加测试结果时都重新计算排名。它当然是功能性的，但不是最佳的。最好的方法是在显示排名时计算排名。如果你想让学生有相同的结果和排名，你可以随时用PHP计算排名。
SQL:
SELECT
UserId,
AsyncPointsAverage,
AsyncPointsAverageRank
FROM
studenttable
ORDER BY
AsyncPointsAverage DESC
PHP:
$stmt = $db->prepare("SEE ABOVE...");
$stmt->execute();
if( $stmt && isset( $stmt ) && $stmt->rowCount() ) {
$rank = 1;
$last_grade = -1;
while( ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ) ) {
$usersARR[$i][0] = intval($row['UserId']);
$usersARR[$i][1] = floatval($row['AsyncPointsAverage']);
$usersARR[$i][2] = floatval($row['AsyncPointsRecentAverage']);
if( $usersARR[$i][1] < $last_grade ) {
$rank++;
}
$usersARR[$i][3] = $rank;
$last_grade = $usersARR[$i][1];
}
}
如果您想按最近的平均值排序，只需要更改字段read和ORDER BY字段。