如何通过Windows PowerShell对SQLite表字段按分组实现自增更新

问题原因 #

你之前使用的UPDATE语句是批量对所有符合条件的行执行Counter = Counter + 2操作，只会把所有符合条件的行的Counter值统一加2，不会生成逐行递增的分组序列，因此无法达到预期效果。

解决方案 #

方案1：窗口函数实现（SQLite 3.33.0及以上版本推荐）

使用SQLite内置的ROW_NUMBER()窗口函数，可以快速生成分组自增序列，针对Symbol为"A"的场景，SQL语句如下：

UPDATE History
SET Counter = (
    SELECT rn FROM (
        SELECT 
            -- 如果需要指定分组内排序规则，比如按Level升序排列，将ORDER BY后内容改为Level ASC即可
            ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn,
            ROWID AS rid
        FROM History
        WHERE Symbol = 'A'
    ) t
    WHERE t.rid = History.ROWID
)
WHERE Symbol = 'A';

如果后续需要适配所有Symbol的分组自增需求，去掉上述语句末尾的WHERE Symbol = 'A'，同时窗口函数部分增加PARTITION BY Symbol即可，示例如下：

UPDATE History
SET Counter = (
    SELECT rn FROM (
        SELECT 
            ROW_NUMBER() OVER (PARTITION BY Symbol ORDER BY (SELECT 1)) AS rn,
            ROWID AS rid
        FROM History
    ) t
    WHERE t.rid = History.ROWID
);

该方案使用SQLite内置隐藏字段ROWID做行匹配，不会出现同值匹配错误的问题，性能也更优。

方案2：低版本SQLite兼容方案

如果你的SQLite版本低于3.33.0不支持窗口函数，可以使用关联子查询实现：

UPDATE History h1
SET Counter = (
    SELECT COUNT(*) + 1
    FROM History h2
    WHERE h2.Symbol = h1.Symbol AND h2.ROWID < h1.ROWID
)
WHERE h1.Symbol = 'A';

.cmd调用方法 #

把上述SQL语句保存为update_counter.sql文件，在.cmd脚本中写入以下命令执行即可：

@echo off
sqlite3.exe 你的数据库文件全路径 < update_counter.sql

注意：执行前可以先运行查询语句确认生成的序列是否符合预期，避免数据更新错误，测试查询语句示例：

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn, Symbol, Level FROM History WHERE Symbol='A';

内容的提问来源于stack exchange，提问作者JTFuzzy