开发者

Performing a prefix computation using SQL without defined procedures

开发者 https://www.devze.com 2023-01-19 16:59 出处:网络
I have a table with a column of integers - I need a way to 开发者_StackOverflow社区generate the \"prefix\" of this column in another table.

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 output

This 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
0

精彩评论

暂无评论...
验证码 换一张
取 消