如何将分数作为另一个表值的平均值插入一个表中 - How can I insert the score into one table as an average of values from another table?

How can I insert the score into one table as an average of values from another table?

这是我从表单中插入分数的第一个查询：

$query1 = "INSERT INTO Category(timing, musicality, technique, difficulty, performance_id)
      VALUES ('{$timing}', '{$musicality}', '{$technique}', '{$difficulty}','{$performance}')"
      ;

这很有效，它将分数放在一个个人分数表中，现在我试图取这些分数的平均值，并将它们放在参赛者信息所在的表中，我为分数留出了一列空白，这就是我尝试的查询：

$query2 = "Insert into Contestants (score) select 
sum(timing + musicality + technique + difficulty)/(select count(timing)* 4 from Category where performance_id = '{$performance}')
From Category
WHERE performance_id = '{$performance}';";

实际情况是，它将正确的平均分数插入到我希望的表中的正确字段中，但它没有根据performance_id更新行，而是创建了一个新行。我该怎么解决这个问题？

试试这个：

$query2 = "UPDATE Contestants SET score = (select 
sum(timing + musicality + technique + difficulty)/(select count(timing)* 4 from Category where performance_id = '{$performance}')
From Category
WHERE performance_id = '{$performance}') 
WHERE performance_id = '{$performance}';";