mysql - 从生成的列创建一个 mysql 累积和列

Question

我有以下查询

SELECT date, t1.col1, t2.col2, t1.col1 * t2.col2 AS `col3`
FROM t1
INNER JOIN t2 ON t2.col3 = t1.col3
WHERE t2.col3 = 2
ORDER BY date ASC

现在我想添加一个列，它是 col3 的累加和，所以根据这个问题 (Create a Cumulative Sum Column in MySQL) 我想出了这个:

SET @csum := 0;
SELECT date, t1.col1, t2.col2, t1.col1 * t2.col2 AS `col3`, (@csum := @csum + `col3`) AS `sum`
FROM t1
INNER JOIN t2 ON t2.col3 = t1.col3
WHERE t2.col3 = 2
ORDER BY date ASC

但是由于 col3 也是在查询中生成的，所以它显然不起作用。我如何更改它以便生成的列可以具有累计总和？

Answer 1

您也可以在 @csum 表达式中使用与 col3 相同的公式，或者您可以使用周围的选择语句添加 sum-列。

所以要么...

SET @csum := 0;
SELECT date, t1.col1, t2.col2, t1.col1 * t2.col2 AS `col3`, (@csum := @csum + (t1.col1 * t2.col2)) AS `sum`
FROM t1
INNER JOIN t2 ON t2.col3 = t1.col3
WHERE t2.col3 = 2
ORDER BY date ASC

或者...

SET @csum := 0;
SELECT ti.*, (@csum := @csum + ti.col3) AS `sum`
FROM (
  SELECT date, t1.col1, t2.col2, t1.col1 * t2.col2 AS `col3`, 
  FROM t1
  INNER JOIN t2 ON t2.col3 = t1.col3
  WHERE t2.col3 = 2
  ORDER BY date ASC) ti