Sqlite 根据前一行的值递增列值 [英] Sqlite Increment column value based on previous row's value

问题描述

如何根据 Sqlite 中的前一列值递增列值?我需要为 1000 多行执行此操作.我在第一行有数据说 100.我需要将接下来的 1000 行增加 2.

How do I increment a column value based on previous column value in Sqlite? I need to do this for 1000+ rows. I have data in the first row say 100. I need to increment the next 1000 rows by 2.

Row#  ColName
1       100
2       102
3       104
4       106

我尝试过这样的事情:更新表 SET ColName = (Select max(ColName ) from Table ) + 2 但这会在所有列中放置 102.

I tried something like this: Update Table SET ColName = (Select max(ColName ) from Table ) + 2 but this puts 102 in all columns.

推荐答案

假设这个表有一个 rowid 列，可以计算前面有多少行:

Assuming that this table has a rowid column, it is possible to count how many previous rows there are:

UPDATE MyTable
SET ColName = (SELECT MAX(ColName)
               FROM MyTable
              ) +
              (SELECT COUNT(*)
               FROM MyTable AS Previous
               WHERE Previous.rowid < MyTable.rowid
              ) * 2
WHERE ColName IS NULL

这篇关于Sqlite 根据前一行的值递增列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！