TSQL填充缺失记录：补全临时表中缺失的ID3关联数据

针对你提到的需求——补全#Temp表中所有缺失的(ID1, ID2, ID3)组合记录，我整理了一套完整的TSQL解决方案，下面一步步来拆解：

先确认原表结构与初始数据 #

首先是你提供的表创建和数据插入代码：

CREATE TABLE #Temp ( 
    [ID1] INT , 
    [ID2] INT , 
    [ID3] INT , 
    [Val] BIT , 
    PRIMARY KEY CLUSTERED ( [ID1], [ID2], [ID3] ) 
) ; 

INSERT INTO #Temp 
SELECT 1 , 10 , 100 , 1 UNION ALL 
SELECT 1 , 10 , 101 , 1 UNION ALL 
SELECT 1 , 11 , 100 , 1 UNION ALL 
SELECT 1 , 11 , 101 , 1 UNION ALL 
SELECT 2 , 10 , 100 , 1 ; 

CREATE TABLE #Temp_ID3 ( [ID3] INT ) ; 

INSERT INTO #Temp_ID3 
SELECT 100 UNION ALL 
SELECT 101 UNION ALL 
SELECT 102 ; 

执行查询查看初始数据：

SELECT [ID1] , [ID2] , [ID3] , [Val] FROM #Temp ;

能看到目前缺少这些关键组合：(1,10,102)、(1,11,102)、(2,10,101)、(2,10,102)，这些就是我们需要补全的记录。

完整补全方案代码 #

要生成并插入这些缺失的组合，我们可以用CTE生成所有可能的组合，再筛选出不存在的记录插入：

-- 生成所有可能的(ID1, ID2, ID3)组合
WITH AllPossibleCombos AS (
    SELECT 
        DISTINCT t.ID1, t.ID2, i.ID3
    FROM #Temp t
    CROSS JOIN #Temp_ID3 i
)
-- 筛选缺失组合并插入到#Temp
INSERT INTO #Temp (ID1, ID2, ID3, Val)
SELECT 
    apc.ID1, apc.ID2, apc.ID3, 0 AS Val -- Val默认设为0，可按需修改
FROM AllPossibleCombos apc
LEFT JOIN #Temp t 
    ON apc.ID1 = t.ID1 
    AND apc.ID2 = t.ID2 
    AND apc.ID3 = t.ID3
WHERE t.ID1 IS NULL; -- 只取#Temp中没有的组合

验证结果 #

插入完成后，执行下面的查询确认所有组合都已补全：

SELECT [ID1] , [ID2] , [ID3] , [Val] FROM #Temp ORDER BY ID1, ID2, ID3;

此时你会看到所有#Temp中已有的(ID1, ID2)对，都和#Temp_ID3里的每一个ID3值形成了完整组合。

代码逻辑解释 #

• AllPossibleCombos CTE：通过CROSS JOIN把#Temp里所有唯一的(ID1, ID2)组合，和#Temp_ID3里的全部ID3值做笛卡尔积，这样就得到了理论上应该存在的所有组合。加DISTINCT是为了避免#Temp里重复的(ID1, ID2)导致生成重复的候选组合。
• 左连接筛选缺失项：把生成的全量组合和#Temp做左连接，那些在#Temp里找不到匹配的行（也就是t.ID1 IS NULL的记录），就是我们要补的缺失组合。
• 插入操作：因为#Temp的聚集主键是(ID1, ID2, ID3)，所以插入的这些组合肯定不会触发主键冲突——毕竟我们筛选的就是原本不存在的记录。Val字段我默认设为0，你可以根据业务需求改成其他值。

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