SQLite表过滤code_b含已存在code_a值行的查询实现方法

先明确匹配示例的过滤规则 #

• 同id分组下的行按原有插入顺序判定
• 若当前行的code_b取值，已在**之前已保留行的code_a**中出现，直接剔除该行
• 否则保留当前行，并将当前行的code_a纳入「已占用code值集合」

注：如果直接按字面规则「只要code_b在全表所有原始行的code_a中出现就剔除」，会误删示例中首行code_b=4的有效行（因后续存在code_a=4的行），不符合业务预期，因此优先采用顺序判定规则。

方案1：仅查询有效结果（不修改原表） #

使用SQLite支持的递归CTE实现顺序遍历与判定：

WITH RECURSIVE row_order AS (
    -- 取每个id下最早插入的首行作为递归起点
    SELECT 
        id, code_a, code_b, rowid,
        code_a AS used_codes,
        1 AS is_valid
    FROM biz_table
    WHERE rowid IN (SELECT MIN(rowid) FROM biz_table GROUP BY id)
    
    UNION ALL
    
    -- 递归遍历同id下的后续行
    SELECT 
        b.id, b.code_a, b.code_b, b.rowid,
        -- 若当前行有效，将其code_a加入已用集合
        CASE WHEN r.is_valid = 1 
             THEN r.used_codes || ',' || b.code_a 
             ELSE r.used_codes 
        END AS used_codes,
        -- 判定当前行code_b是否已在已用集合中
        CASE WHEN (',' || r.used_codes || ',') NOT LIKE ('%,' || b.code_b || ',%') 
             THEN 1 ELSE 0 
        END AS is_valid
    FROM biz_table b
    INNER JOIN row_order r 
        ON b.id = r.id AND b.rowid > r.rowid
    -- 按插入顺序逐行遍历，避免跳行
    WHERE b.rowid = (SELECT MIN(rowid) FROM biz_table WHERE id = b.id AND rowid > r.rowid)
)
-- 输出所有有效行
SELECT id, code_a, code_b FROM row_order WHERE is_valid = 1;

注：SQL中已用code前后拼接逗号的处理，是为了避免出现1匹配10这类数值前缀误判的问题，确保匹配精准。

适配说明 #

• 若行顺序通过自定义字段（如create_time、sort_num）标识，将SQL中所有rowid替换为对应顺序字段即可。
• 若原表为WITHOUT ROWID表，需指定自定义的顺序字段替换rowid。
• 若需使用字面规则（不考虑顺序），简化SQL如下（注意会出现示例中的误删问题）：

  SELECT * FROM biz_table
  WHERE code_b NOT IN (SELECT DISTINCT code_a FROM biz_table);
  

方案2：重建表永久清理冗余数据 #

若需永久删除原表冗余数据，通过新建临时表迁移有效数据的方式最稳妥（SQLite不支持直接基于窗口函数删除行）：

1. 创建结构与原表完全一致的新表：

CREATE TABLE biz_table_clean (
    id INTEGER,
    code_a INTEGER,
    code_b INTEGER
);

2. 迁移有效数据到新表：

INSERT INTO biz_table_clean (id, code_a, code_b)
WITH RECURSIVE row_order AS (
    SELECT id, code_a, code_b, rowid, code_a AS used_codes, 1 AS is_valid
    FROM biz_table
    WHERE rowid IN (SELECT MIN(rowid) FROM biz_table GROUP BY id)
    
    UNION ALL
    
    SELECT b.id, b.code_a, b.code_b, b.rowid,
        CASE WHEN r.is_valid = 1 THEN r.used_codes || ',' || b.code_a ELSE r.used_codes END,
        CASE WHEN (',' || r.used_codes || ',') NOT LIKE ('%,' || b.code_b || ',%') THEN 1 ELSE 0 END
    FROM biz_table b
    INNER JOIN row_order r ON b.id = r.id AND b.rowid > r.rowid
    WHERE b.rowid = (SELECT MIN(rowid) FROM biz_table WHERE id = b.id AND rowid > r.rowid)
)
SELECT id, code_a, code_b FROM row_order WHERE is_valid = 1;

3. 替换原表（操作前务必备份原表）：

-- 备份原表
ALTER TABLE biz_table RENAME TO biz_table_backup;
-- 重命名新表为原表名
ALTER TABLE biz_table_clean RENAME TO biz_table;
-- 确认数据无误后可删除备份表
-- DROP TABLE biz_table_backup;

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