我在构建聚合函数时遇到问题。我的问题是：
我有一张这样的桌子
id action day isSum difference
1 ping 2012-01-01 1 500 (this is the sum of the differences from last year)
2 ping 2012-01-01 0 -2
3 ping 2012-01-02 0 1
4 ping 2012-01-03 0 -4
5 ping 2012-01-04 0 -2
6 ping 2012-01-05 0 3
7 ping 2012-01-06 0 2
8 ping 2012-01-01 1 0 (this is the sum of the differences from last year, now for pong)
9 pong 2012-01-01 0 -5
10 pong 2012-01-02 0 2
11 pong 2012-01-03 0 -2
12 pong 2012-01-04 0 -8
13 pong 2012-01-05 0 3
14 pong 2012-01-06 0 4
我现在需要为每一天选择操作、日期和01-01以来的汇总差异，以便我的结果如下所示
action day total
ping 2012-01-01 498
ping 2012-01-02 499
ping 2012-01-03 495
ping 2012-01-04 493
ping 2012-01-05 496
ping 2012-01-06 498
pong 2012-01-01 - 5
pong 2012-01-02 - 3
pong 2012-01-03 - 5
pong 2012-01-04 -13
pong 2012-01-05 -10
pong 2012-01-06 - 6
我该怎么做？
有很多数据集（~100万），所以查询需要非常便宜。我不知道如何使用sum根据action列获取每日记录的每日总和。
最佳答案
您需要使用SUBQUERY来获取total字段。
select action, day,
(select sum(difference) from x
where action = t2.action and day <= t2.day
group by action) as total
from x t2 group by action ,day
在SQLFIDDLE上查看
关于mysql - 建立基于SUM的每日记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12454983/