SQLite：SELECT 语句中的累加器（总和）列

发布于 2024-09-24 21:58:30 字数 259 浏览 7 评论 0原文

我有一张这样的桌子：

从表中选择值；

value
1
3
13
1
5

我想添加一个累加器列，以便得到以下结果：

value  accumulated
1      1
3      4
13     17
1      18
5      23

我该怎么做？我想做的事情的真实名称是什么？谢谢

原文

I have a table like this one:

SELECT value FROM table;

value
1
3
13
1
5

I would like to add an accumulator column, so that I have this result:

value  accumulated
1      1
3      4
13     17
1      18
5      23

How can I do this? What's the real name of what I want to do? Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

怪我闹别瞎闹 2024-10-01 21:58:30

尝试这种方式：

select value,
(select sum(t2.value) from table t2 where t2.id <= t1.id ) as accumulated
from table t1

但如果它在你的数据库上不起作用，只需添加

select value,
(select sum(t2.value) from table t2 where t2.id <= t1.id order by id ) as accumulated
from table t1
order by id

在 oracle 上起作用的命令；）但它也应该在 sqlite 上

try this way:

select value,
(select sum(t2.value) from table t2 where t2.id <= t1.id ) as accumulated
from table t1

but if it will not work on your database, just add order by something

select value,
(select sum(t2.value) from table t2 where t2.id <= t1.id order by id ) as accumulated
from table t1
order by id

this works on an oracle ;) but it should on a sqlite too

回复收藏 0 原文

追星践月 2024-10-01 21:58:30

这是一种创建运行总计的方法，而不会出现对所有先前行进行求和的低效率情况。（我知道这个问题已经有 6 年历史了，但它是 SQLite 运行总计的第一个 google 条目之一。）

create table t1 (value integer, accumulated integer, id integer primary key);
insert into t1 (value) values (1);
insert into t1 (value) values (3);
insert into t1 (value) values (13);
insert into t1 (value) values (1);
insert into t1 (value) values (5);

UPDATE
    t1
SET
    accumulated = ifnull(
    (
        SELECT
            ifnull(accumulated,0)
        FROM
            t1 ROWPRIOR
        WHERE
            ROWPRIOR.id = (t1.id -1 )),0) + value;


.headers on
select * from t1;
value|accumulated|id
1|1|1
3|4|2
13|17|3
1|18|4
5|23|5

这应该只在导入所有值后运行一次。或者，在再次运行之前将累积列设置为所有空值。

Here's a method to create a running total without the inefficiency of summing all prior rows. (I know this question is 6 years old but it's one of the first google entries for sqlite running total.)

create table t1 (value integer, accumulated integer, id integer primary key);
insert into t1 (value) values (1);
insert into t1 (value) values (3);
insert into t1 (value) values (13);
insert into t1 (value) values (1);
insert into t1 (value) values (5);

UPDATE
    t1
SET
    accumulated = ifnull(
    (
        SELECT
            ifnull(accumulated,0)
        FROM
            t1 ROWPRIOR
        WHERE
            ROWPRIOR.id = (t1.id -1 )),0) + value;


.headers on
select * from t1;
value|accumulated|id
1|1|1
3|4|2
13|17|3
1|18|4
5|23|5

This should only be run once after importing all the values. Or, set the accumulated column to all nulls before running again.

回复收藏 0 原文