不依赖RowId序列的SQLite累计求和问题咨询

从你的描述来看，核心问题是原来的递归CTE依赖id（也就是RowId）的递增顺序计算累计和，但插入历史月份的记录时，新记录的id会比现有记录大，导致它被排在遍历顺序的最后，破坏了按year、month排序的累计逻辑。下面给你两种解决方案，优先推荐更简洁高效的窗口函数方案：

方案一：使用窗口函数（SQLite 3.25+ 推荐） #

SQLite从3.25版本开始支持窗口函数，这和你在SQL Server中用SUM() OVER()的逻辑几乎一致，完全不需要递归，直接按year和month的顺序计算累计和：

SELECT 
    id, 
    month, 
    year, 
    value,
    -- 按year、month、id排序，保证累计顺序稳定
    SUM(value) OVER (ORDER BY year, month, id) AS rt
FROM Test
ORDER BY year, month, id;

这里额外加入id作为排序条件，是为了处理同年同月有多条记录的情况，避免累计顺序出现混乱。不管你什么时候插入哪个月份的记录，只要year和month值正确，累计和都会自动按时间顺序计算，完全不受id大小的影响。

方案二：调整递归CTE逻辑（兼容旧版本SQLite） #

如果你使用的是不支持窗口函数的旧版SQLite，可以修改递归CTE的遍历逻辑，不再依赖id的大小，而是按year和month的时间顺序来遍历记录：

WITH RECURSIVE running AS (
    -- 起始点：取时间最早的那条记录（按year、month、id排序）
    SELECT 
        id, 
        month, 
        year, 
        value, 
        value AS rt
    FROM Test
    ORDER BY year, month, id
    LIMIT 1

    UNION ALL

    -- 递归部分：找到当前记录之后时间最早的下一条记录
    SELECT 
        t.id, 
        t.month, 
        t.year, 
        t.value, 
        t.value + r.rt AS rt
    FROM running r
    JOIN Test t ON 
        -- 匹配时间晚于当前记录的条件
        (t.year > r.year) 
        OR (t.year = r.year AND t.month > r.month)
        OR (t.year = r.year AND t.month = r.month AND t.id > r.id)
    WHERE NOT EXISTS (
        -- 确保中间没有更早的记录，保证取到的是真正的下一条
        SELECT 1 FROM Test t2
        WHERE 
            (t2.year > r.year AND t2.year < t.year)
            OR (t2.year = r.year AND t2.month > r.month AND t2.month < t.month)
            OR (t2.year = r.year AND t2.month = r.month AND t2.id > r.id AND t2.id < t.id)
    )
)
SELECT * FROM running
ORDER BY year, month, id;

这个递归CTE的逻辑是：每次找到当前记录之后时间最早的那条记录，而不是id最大的那条。这样即使插入了历史月份的记录（id很大但时间更早），遍历顺序也会按year、month自动调整，累计和自然就正确了。

补充说明 #

从SQL Server转过来的话，窗口函数应该是最符合你使用习惯的方案，它不仅代码简洁，执行效率也比递归CTE高很多。如果你的SQLite版本允许，一定要优先选择这个方案。

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