如何在一个 MySQL 数据库更新中执行多个数学方程式？

Question

我有一个 MySQL 数据库行，用于存储游戏的尝试次数和平均得分：+-----+----------+-----------+| uid | attempts | avg_score ||  4  |    3     |    15     |+-----+----------+-----------+用户完成游戏后，我想更新这两个表以根据新的尝试计算出新的平均值。我想：乘以（尝试* avg_score = 45）将trial_score添加到总avg_score（avg_score（45）+ trial_score（5）= 50）除以（avg_score（45）+ trial_score（5）/尝试=尝试+ 1）对于 PHP mySQL 语句，这有点让我头疼。我将在下面展示我的尝试。$sql=("UPDATE gamescore SET attempts = attempts + 1,avg_score = ((attempts * avg_score + ?) / (attempts = attempts + 1)) WHERE uid=?");$stmt = $conn->prepare($sql);$stmt->bind_param("ii",$_POST['trial_score'],$_SESSION['uid']);$stmt->execute();失败..显然...我需要在这里更正什么？谢谢！

Answer 1

你必须除以(attempts + 1)而不是(attempts = attempts + 1)。

还要设置avg_scorefirst 和 then的值，attempts因为如果先设置attempts，则设置 MySql 值的表达式中的值avg_score将使用修改后的值attempts（您可以在此处找到更多信息）：

UPDATE gamescore  

SET avg_score = (attempts * avg_score + ?) / (attempts + 1),

    attempts = attempts + 1 

WHERE uid = ?;