求SQLite中填充code_task关联表的SQL查询语句

初始填充code_task表 #

如果是第一次填充表数据，可以直接使用以下SQL语句，将Codes表中符合条件的code_id与对应task_id插入到code_task表：

INSERT INTO code_task (code_id, task_id)
-- 匹配for_2D=1的code，关联到task_id=1（2D任务）
SELECT code_id, 1 AS task_id
FROM Codes
WHERE for_2D = 1
UNION ALL
-- 匹配for_3D=1的code，关联到task_id=2（3D任务）
SELECT code_id, 2 AS task_id
FROM Codes
WHERE for_3D = 1;

这条语句会生成所有符合预期的关联对：

• 仅for_2D=1的code会插入(code_id, 1)
• 仅for_3D=1的code会插入(code_id, 2)
• 两个字段都为1的code会插入两行，对应两个task_id

同步关系变更（仅更新变化的关联） #

当Codes表中的for_2D或for_3D字段发生变更时，可通过先删除失效关联，再插入新增关联的方式，确保code_task表的关联关系始终准确：

批量同步所有Codes的关联 #

-- 第一步：删除所有不再有效的关联记录
DELETE FROM code_task
WHERE NOT EXISTS (
    SELECT 1
    FROM Codes c
    WHERE c.code_id = code_task.code_id
    AND (
        (code_task.task_id = 1 AND c.for_2D = 1)
        OR (code_task.task_id = 2 AND c.for_3D = 1)
    )
);

-- 第二步：插入新增的关联（避免重复插入已存在的记录）
INSERT OR IGNORE INTO code_task (code_id, task_id)
SELECT code_id, 1 AS task_id
FROM Codes
WHERE for_2D = 1
UNION ALL
SELECT code_id, 2 AS task_id
FROM Codes
WHERE for_3D = 1;

单个Code的关联同步 #

如果仅需更新特定code_id的关联，可使用带参数的SQL（?为参数占位符，替换为目标code_id）：

-- 删除该code_id下失效的关联
DELETE FROM code_task
WHERE code_id = ?
AND task_id NOT IN (
    SELECT 1 WHERE (SELECT for_2D FROM Codes WHERE code_id = ?) = 1
    UNION
    SELECT 2 WHERE (SELECT for_3D FROM Codes WHERE code_id = ?) = 1
);

-- 插入该code_id下新增的关联
INSERT OR IGNORE INTO code_task (code_id, task_id)
SELECT ?, 1 WHERE (SELECT for_2D FROM Codes WHERE code_id = ?) = 1
UNION ALL
SELECT ?, 2 WHERE (SELECT for_3D FROM Codes WHERE code_id = ?) = 1;

补充说明 #

• 建议给code_task表添加(code_id, task_id)的唯一约束，确保关联关系唯一，INSERT OR IGNORE会自动忽略已存在的记录，避免主键冲突报错。
• 批量同步语句可定期执行，或在Codes表的for_2D/for_3D字段更新后触发执行，保证关联关系实时同步。

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