如何在SQLite中连接行数相同的有序表并保留行序？

嘿，这个场景我太熟悉了！WITH（包括递归WITH）生成的临时表确实没有内置的rowid可用，没法直接像MSSQL那样靠rowid关联。不过咱们可以通过给每张表生成对应行序的自增行号来解决，核心思路就是用行号作为两张表的关联键，保证逐行对应且保留原顺序。

优先方案：使用窗口函数（SQLite 3.25.0+） #

从SQLite 3.25.0版本开始支持窗口函数，ROW_NUMBER()是最直接的工具。你只需要给每个WITH表加上行号，注意必须用原表的排序字段来指定ORDER BY，这样行号才能严格匹配原表的行序。

举个实际例子，假设你的两个WITH表是这样生成的：

WITH 
-- 第一个有序表：生成1到5的数字
cte_numbers AS (
    SELECT value FROM generate_series(1, 5)
),
-- 第二个有序表：生成对应数字的两倍值
cte_doubles AS (
    SELECT value * 2 AS doubled_value FROM generate_series(1, 5)
),
-- 给第一个表加行号（ORDER BY匹配原表的排序逻辑）
cte_numbers_rn AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY value) AS row_id
    FROM cte_numbers
),
-- 给第二个表加行号（同理，ORDER BY要和原表行序一致）
cte_doubles_rn AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY doubled_value) AS row_id
    FROM cte_doubles
)
-- 按行号关联两张表
SELECT 
    cte_numbers_rn.value,
    cte_doubles_rn.doubled_value
FROM cte_numbers_rn
INNER JOIN cte_doubles_rn ON cte_numbers_rn.row_id = cte_doubles_rn.row_id;

如果是递归WITH生成的表，比如递归生成的层级数据，只需要把ORDER BY换成递归时的递增值（比如递归中的level字段）就行，保证行号和递归生成的顺序完全对应。

兼容旧版本方案：子查询计数生成行号 #

如果你的SQLite版本低于3.25.0，没法用窗口函数，可以用子查询计数的方式模拟行号。原理是统计当前行之前（包括当前行）的行数，以此作为行号：

WITH 
cte_numbers AS (
    SELECT value FROM generate_series(1, 5)
),
cte_doubles AS (
    SELECT value * 2 AS doubled_value FROM generate_series(1, 5)
),
cte_numbers_rn AS (
    SELECT 
        value,
        -- 统计当前value之前的行数，生成行号
        (SELECT COUNT(*) FROM cte_numbers c WHERE c.value <= cte_numbers.value) AS row_id
    FROM cte_numbers
),
cte_doubles_rn AS (
    SELECT 
        doubled_value,
        (SELECT COUNT(*) FROM cte_doubles c WHERE c.doubled_value <= cte_doubles.doubled_value) AS row_id
    FROM cte_doubles
)
SELECT * FROM cte_numbers_rn
INNER JOIN cte_doubles_rn ON cte_numbers_rn.row_id = cte_doubles_rn.row_id;

⚠️ 注意：这种方法的效率比窗口函数低，数据量大的时候会有明显差异，所以优先升级SQLite版本用窗口函数方案。

最后再强调一下：因为你已经确认两张表行数完全相同，所以用INNER JOIN就足够，不会出现行不匹配的情况，完美保留原有的行序。

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