mysql - 具有聚合字段和分组依据的临时变量在 mysql 中不起作用

Question

我正在尝试使用临时变量获取列的累积值。

SELECT sum(price), @temp := @temp + sum(price) AS cumulative_price  FROM `table`, (SELECT @temp := 0) B GROUP BY item 

它在没有分组依据和聚合字段时起作用。但是，当有group by字段时，cumulative_price的值与sum(price)相同，这不是预期的。

造成这种不一致的原因是什么？

Answer 1

它不应该工作。根据Doc

In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected:

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

The reference to b in the HAVING clause refers to an alias for an expression in the select list that uses @aa. This does not work as expected: @aa contains the value of id from the previous selected row, not from the current row.

因此，当您定义变量时，它将适用于当前行而不是行集