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
- 第1、3、5...(奇数位)的
标准版本实现(≥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
相关产品推荐
相关产品推荐

