You need to enable JavaScript to run this app. SQLite 如何用纯SQL基于flag值按顺序配对记录ID为两列
优惠活动
大模型
产品
解决方案
定价
更多

SQLite 如何用纯SQL基于flag值按顺序配对记录ID为两列

实现结论

完全可以通过纯SQLite3语句实现,无需依赖宿主应用后处理。常规场景依赖SQLite窗口函数能力,要求版本≥3.25.0;低版本也可通过自连接逻辑实现。

对齐期望的实现逻辑

匹配你给出的输出规则:

  • 所有flag=0的原始行全部保留,id2列返回NULL
  • flag=1的行按id升序排序后两两配对:
    • 第1、3、5...(奇数位)的flag=1行保留为id1列,id2列取同组下一个(偶数位)flag=1行的id
    • 第2、4、6...(偶数位)的flag=1行仅作为配对值出现在其他行的id2列,不单独输出为id1
    • 若flag=1行总数为奇数,最后一个奇数位的flag=1行无配对对象,id2返回NULL
标准版本实现(≥3.25.0,支持窗口函数)

直接运行以下SQL即可得到和你给出的期望完全一致的结果:

WITH flag1_sorted AS (
    -- 为所有flag=1的行按id升序生成连续行号
    SELECT
        id,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM test
    WHERE flag = 1
),
flag1_paired AS (
    -- 奇数位行取后一行的id作为配对值,偶数位行标记为待过滤
    SELECT
        id,
        rn,
        CASE WHEN rn % 2 = 1 THEN LEAD(id) OVER (ORDER BY id) ELSE NULL END AS matched_id
    FROM flag1_sorted
)
SELECT
    t.id AS id1,
    fp.matched_id AS id2
FROM test t
LEFT JOIN flag1_paired fp ON t.id = fp.id
-- 过滤掉作为配对值的偶数位flag=1行
WHERE NOT (t.flag = 1 AND fp.rn % 2 = 0)
ORDER BY t.id;

运行输出结果:

id1 | id2
----+-----
  1 | null
  2 | null
  3 |   5
  4 | null
  6 | null
  7 | null
可选调整

如果需要输出全部原始行的id1列(即不屏蔽作为配对值的偶数位flag=1行),删除上述SQL最后一行的WHERE过滤条件即可,运行后会额外输出一行5 | null,对应id=5的原始记录。

低版本兼容方案(<3.25.0,无窗口函数)

如果SQLite版本过低不支持窗口函数,可通过自连接生成行号的方式实现,逻辑完全一致:

WITH flag1_sorted AS (
    -- 自连接生成flag=1行的连续行号
    SELECT
        t1.id,
        COUNT(t2.id) + 1 AS rn
    FROM test t1
    LEFT JOIN test t2 ON t1.id > t2.id AND t2.flag = 1
    WHERE t1.flag = 1
    GROUP BY t1.id
),
flag1_paired AS (
    SELECT
        id,
        rn,
        -- 奇数位行查询rn+1的行id作为配对值
        CASE WHEN rn % 2 = 1 THEN 
            (SELECT id FROM flag1_sorted WHERE rn = t.rn + 1) 
        ELSE NULL END AS matched_id
    FROM flag1_sorted t
)
SELECT
    t.id AS id1,
    fp.matched_id AS id2
FROM test t
LEFT JOIN flag1_paired fp ON t.id = fp.id
WHERE NOT (t.flag = 1 AND fp.rn % 2 = 0)
ORDER BY t.id;

内容的提问来源于stack exchange,提问作者Jeff Godfrey

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.06 05:45:02