Heim > Fragen und Antworten > Hauptteil
P粉2452767692023-08-23 09:47:13
SELECT t.id, t.count, (SELECT SUM(x.count) FROM TABLE x WHERE x.id <= t.id) AS cumulative_sum FROM TABLE t ORDER BY t.id
SELECT t.id, t.count, @running_total := @running_total + t.count AS cumulative_sum FROM TABLE t JOIN (SELECT @running_total := 0) r ORDER BY t.id
注意:
JOIN (SELECT @running_total := 0) r
是一个交叉连接,允许在不需要单独的SET
命令的情况下声明变量。r
注意事项:
ORDER BY
非常重要,它确保顺序与原始问题匹配,并且对于更复杂的变量使用(例如:伪ROW_NUMBER/RANK功能,MySQL不支持)可能会有更大的影响P粉0065406002023-08-23 00:13:27
如果性能是一个问题,你可以使用MySQL变量:
set @csum := 0; update YourTable set cumulative_sum = (@csum := @csum + count) order by id;
或者,你可以移除cumulative_sum
列,并在每个查询中计算它:
set @csum := 0; select id, count, (@csum := @csum + count) as cumulative_sum from YourTable order by id;
这样以一种连续的方式计算累积和 :)