SQL 多表關聯：判斷用戶缺少的編碼並批量補全插入

問題描述

表 A：用戶擁有的編碼記錄 `(id, no, name, status)`。
表 C：標準編碼庫 `(no)`。
需求：找出每個用戶缺少的編碼，並以 `status='add'` 批量插入表 A。

數據示例

表 A：
(1, 'A', '張三', 'add')
(1, 'B', '張三', 'del')  -- 已刪除，視為不擁有
(2, 'A', '李四', 'add')
(3, 'B', '王五', 'update')

表 C：
('A'), ('B'), ('C')

預期結果：
張三 缺 C → 插入 (1, 'C', '張三', 'add')
李四 缺 B、C → 插入 (2, 'B', '李四', 'add'), (2, 'C', '李四', 'add')
王五 缺 A、C → 插入 (3, 'A', '王五', 'add'), (3, 'C', '王五', 'add')

核心邏輯：笛卡爾積 + 排除現有

1. 獲取所有用戶：`SELECT DISTINCT id, name FROM A WHERE status <> 'del'`
2. 與編碼庫交叉連接：`CROSS JOIN (SELECT no FROM C)` → 產生「用戶-編碼」完整組合。
3. 排除已擁有（含 `status='del'` 視為不擁有）：`WHERE (id, no) NOT IN (SELECT id, no FROM A WHERE status <> 'del')`。
4. 插入目標表。

標準 SQL 實現（兼容主流數據庫）

INSERT INTO A (id, no, name, status)
SELECT u.id, c.no, u.name, 'add'
FROM (
    SELECT DISTINCT id, name
    FROM A
    WHERE status <> 'del'
) u
CROSS JOIN C c
WHERE NOT EXISTS (
    SELECT 1
    FROM A a2
    WHERE a2.id = u.id
      AND a2.no = c.no
      AND a2.status <> 'del'
);

MySQL / SQL Server 方言寫法

-- MySQL: 使用 NOT IN (需注意 NULL 值陷阱，建議用 NOT EXISTS)
INSERT INTO A (id, no, name, status)
SELECT u.id, c.no, u.name, 'add'
FROM (SELECT DISTINCT id, name FROM A WHERE status <> 'del') u
CROSS JOIN C c
WHERE (u.id, c.no) NOT IN (SELECT id, no FROM A WHERE status <> 'del');

-- SQL Server: 使用 MERGE (2008+) 實現 Upsert
MERGE INTO A AS target
USING (
    SELECT u.id, c.no, u.name
    FROM (SELECT DISTINCT id, name FROM A WHERE status <> 'del') u
    CROSS JOIN C c
) AS source
ON target.id = source.id AND target.no = source.no
WHEN NOT MATCHED THEN
    INSERT (id, no, name, status) VALUES (source.id, source.no, source.name, 'add');

原最佳回答代碼解析

insert into ma(id, fe, vname, 狀態字段)
values(
    SELECT id, vname, fe, 'add'
    FROM (
        SELECT c.*, c.vname + ',' + c.fe as kid
        FROM (
            SELECT * FROM (
                SELECT DISTINCT id, vname FROM ma
            ) a, (SELECT fe FROM mb) b
        ) c
    ) D
    WHERE kid NOT IN (SELECT VNAME + ',' + FE FROM ma WHERE 狀態字段 <> 'del')
)

• 使用字符串拼接 `vname + ',' + fe` 模擬復合鍵比較（舊版本不支持行構造器）。
• 子查詢 `a, b` 隱式交叉連接產生笛卡爾積。
• 外層 `WHERE kid NOT IN ...` 排除已存在記錄。

性能優化建議

• 為 `A(id, no)` 建立唯一索引，加速 `NOT EXISTS` 子查詢。
• 大數據量時，分批提交（如每 5000 行），避免長事務鎖表。
• 使用臨時表/表變量暫存笛卡爾積結果，減少重複計算。

避坑指南

1. NULL 值陷阱：`NOT IN` 子查詢若含 NULL，結果為空。務必用 `NOT EXISTS` 或在子查詢加 `WHERE col IS NOT NULL`。
2. 狀態語義：明確 `del` 是否代表「曾擁有但刪除」（不補充）還是「從未擁有」（需補充）。本文假設前者。
3. 併發安全：高併發場景下，需加表鎖或使用 `INSERT IGNORE` / `ON DUPLICATE KEY UPDATE` 保冪等性。