如何通过SQL或SSIS实现分层表缺失层级的记录生成？

咱们来搞定这个分层数据对比生成记录的问题！先把核心需求再明确下：针对Division→State→County→City这四层分层结构，只要Table A里某一层级的字段组合（比如仅Division、Division+State等）在Table B的对应层级里找不到，就需要生成从最高层级到当前层级的所有记录。下面分别用SQL和SSIS两种方案来实现：

一、SQL实现方案 #

思路 #

先把两个表的所有分层组合拆出来（从仅Division的L1层，到全字段的L4层），然后找出Table A有但Table B没有的分层组合，最后去重得到所有需要生成的记录。

具体代码 #

-- 生成Table A的所有层级拆分记录
WITH A_Hierarchies AS (
    -- L1: 仅Division
    SELECT Division, CAST(NULL AS VARCHAR(50)) AS State, CAST(NULL AS VARCHAR(50)) AS County, CAST(NULL AS VARCHAR(50)) AS City, 1 AS Level
    FROM TableA
    UNION
    -- L2: Division + State
    SELECT Division, State, CAST(NULL AS VARCHAR(50)) AS County, CAST(NULL AS VARCHAR(50)) AS City, 2 AS Level
    FROM TableA
    UNION
    -- L3: Division + State + County
    SELECT Division, State, County, CAST(NULL AS VARCHAR(50)) AS City, 3 AS Level
    FROM TableA
    UNION
    -- L4: 全字段组合
    SELECT Division, State, County, City, 4 AS Level
    FROM TableA
),
-- 生成Table B的所有层级拆分记录
B_Hierarchies AS (
    SELECT Division, CAST(NULL AS VARCHAR(50)) AS State, CAST(NULL AS VARCHAR(50)) AS County, CAST(NULL AS VARCHAR(50)) AS City, 1 AS Level
    FROM TableB
    UNION
    SELECT Division, State, CAST(NULL AS VARCHAR(50)) AS County, CAST(NULL AS VARCHAR(50)) AS City, 2 AS Level
    FROM TableB
    UNION
    SELECT Division, State, County, CAST(NULL AS VARCHAR(50)) AS City, 3 AS Level
    FROM TableB
    UNION
    SELECT Division, State, County, City, 4 AS Level
    FROM TableB
),
-- 筛选出Table A有但Table B没有的层级记录
Missing_Records AS (
    SELECT a.Division, a.State, a.County, a.City
    FROM A_Hierarchies a
    LEFT JOIN B_Hierarchies b
        ON a.Level = b.Level
        AND a.Division = b.Division
        AND (a.State = b.State OR (a.State IS NULL AND b.State IS NULL))
        AND (a.County = b.County OR (a.County IS NULL AND b.County IS NULL))
        AND (a.City = b.City OR (a.City IS NULL AND b.City IS NULL))
    WHERE b.Division IS NULL
)
-- 去重后得到最终结果
SELECT DISTINCT Division, State, County, City
FROM Missing_Records
ORDER BY Division, State, County, City;

关键说明 #

• 用CTE拆分每个表的所有层级，确保覆盖从L1到L4的所有组合；
• 左连接时要处理NULL的匹配（SQL中NULL不等于NULL，所以需要额外判断双方都为NULL的情况）；
• 最后用DISTINCT去重，因为不同层级的缺失可能会生成重复的记录（比如L4缺失会生成L1记录，L3缺失也会生成L1记录）。

二、SSIS实现方案 #

思路 #

通过SSIS的转换组件拆分两个表的层级记录，用查找组件找出缺失的组合，最后合并去重得到结果。

具体步骤 #

1. 配置数据源：新建SSIS包，添加两个OLE DB源，分别连接Table A和Table B；
2. 拆分层级记录：为每个数据源添加脚本组件（转换），生成4个输出（L1~L4）：
   • L1输出：仅保留Division，State/County/City设为NULL；
   • L2输出：保留Division+State，County/City设为NULL；
   • L3输出：保留Division+State+County，City设为NULL；
   • L4输出：保留所有字段；
     脚本组件的C#示例代码：

   public override void Input0_ProcessInputRow(Input0Buffer Row)
   {
       // 输出L1记录
       L1Buffer.AddRow();
       L1Buffer.Division = Row.Division;
   
       // 输出L2记录
       L2Buffer.AddRow();
       L2Buffer.Division = Row.Division;
       L2Buffer.State = Row.State;
   
       // 输出L3记录
       L3Buffer.AddRow();
       L3Buffer.Division = Row.Division;
       L3Buffer.State = Row.State;
       L3Buffer.County = Row.County;
   
       // 输出L4记录
       L4Buffer.AddRow();
       L4Buffer.Division = Row.Division;
       L4Buffer.State = Row.State;
       L4Buffer.County = Row.County;
       L4Buffer.City = Row.City;
   }
   

3. 查找缺失记录：对每个层级的A输出，添加查找组件连接对应的B层级输出，设置匹配条件（比如L1匹配Division，L2匹配Division+State等），选择“将找不到匹配项的行发送至错误输出”，收集这些错误输出；
4. 合并去重：用联合全部（Union All）组件合并所有层级的缺失记录，再添加排序组件，按Division→State→County→City排序并勾选“移除重复项”；
5. 输出结果：将去重后的记录输出到目标表。

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