I have a table with a column of integers - I need a way to 开发者_StackOverflow社区generate the "prefix" of this column in another table.
For e.g.
I have 1, 0, 0, 0, 1, 0, 1, 0, 0 as the input
I need 1, 1, 1, 1, 2, 2, 3, 3, 3 as the outputThis needs to be done in SQLite's SQL dialect , no user defined functions or stored procedures are possible.
try something like this:
select value,
(select sum(t2.value) from table t2 where t2.id <= t1.id ) as accumulated
from table t1
from: SQLite: accumulator (sum) column in a SELECT statement
So to insert from input
table to output
table you need following query:
INSERT INTO output
SELECT id,
(SELECT sum(i1.value) FROM input AS i1 WHERE i1.rowid <= i2.rowid) as VALUE
FROM input AS i2
精彩评论