php - 更新记录时出错 : Invalid use of group function for updating previous all data

Question

$sql = "UPDATE purchase SET stat='1' WHERE `id`='$id' AND 4*(plan)<= sum(plan) ";

或者

$sql = "UPDATE purchase SET stat='1' WHERE `id`='$id' AND 4*(plan-1)<= sum(plan-1) ";

我试图通过此更新进行查询，如果所有行的总和大于第一行，它将更新第一行，并且此规则适用于所有行。但它显示错误并显示

Error updating record: Invalid use of group function 

此查询有什么问题？我需要有关此错误的帮助。

Answer 1

您不能在查询的 WHERE 子句中使用诸如 SUM() 之类的聚合函数，因为 SUM() 是经过计算的覆盖所有选定的行，但使用 WHERE 来选择行。您需要在单独的查询中计算总和，然后加入它。

UPDATE purchase
JOIN (SELECT SUM(plan) AS plantotal FROM purchase) AS p1
    ON 4*(plan-1) <= plantotal
SET stat = '1'
WHERE id = '$id'

要将更新基于具有较高 ID 的所有行，您可以使用子查询来连接，该子查询获取每组行的总计。

UPDATE purchase AS p
JOIN (SELECT p1.id AS id, SUM(p2.plan) AS plantotal
      FROM purchase AS p1
      JOIN purchase AS p2 ON p2.id > p1.id) AS p3
ON p3.id = p.id AND 4*(p.plan-1) <= p3.plantotal
SET stat = '1'