sqlite - SQLite中的累积SUM

Question

我在SQLite中编写了一个查询，该查询累积了VALUE，如果仅按ID排序（ID为自动增量int），则很好。但是，如果我要先按DATE再按ID排序，查询将显示错误的结果。有任何想法吗？

查询：

select t1.ID, 
       t1.DATE, 
       t1.VALUE, 
       sum(t2.VALUE) as TOTAL     
from test t1
inner join test t2 on t1.DATE >= t2.DATE and t1.id >= t2.id 
group by t1.ID, t1.DATE
order by t1.DATE desc, t1.ID desc

结果：

ID  DATE        VALUE   TOTAL
9   2015-01-16  55      281 -- should be 226 + 55 = 281
6   2015-01-15  10      26  -- should be 216 + 10 = 226
5   2015-01-15  5       16  -- should be 211 +  5 = 216
8   2015-01-14  100     211
7   2015-01-14  100     111
4   2015-01-13  5       11
3   2015-01-12  3       6
2   2015-01-11  2       3
1   2015-01-10  1       1

Answer 1

至少在小数据上，将联接更改为以下形式：

t1.DATE||substr('00000'||t1.id,-5) >= t2.DATE||substr('00000'||t2.id,-5)

可以正常工作...因此，我们基于日期和id（用0到5位数字填充）建立一个“排序键”，并在联接中使用它。尽管可能需要在该索引上建立索引以处理性能