动态基于Table2非空字段关联Table1并删除匹配记录方案咨询

现有Table1数据 #

Col1 col2 col3 col4 col5 col6
1    a    b    c    d     4
1    a    b    f    g     4
2    a    i    j    k     5
2    l    m    n    o     4
3    l    m    n    o     4
3    p    q    q    s     6
3    t    u    v    w     7

现有Table2数据 #

RowNumber col1 col2    col3  col4  col5  count_of_records
1           1    a     null  null  null   2
2           2    a     i     null  null   1
3           3    null  null  null  null   3

需求说明 #

需借助Table2的记录删除Table1中的数据，规则如下：

• 针对Table2的每一行，仅使用该行非空字段与Table1进行关联匹配
• 统计Table1中符合匹配条件的记录数
• 若统计数与Table2该行的count_of_records值一致，则删除Table1中所有符合该匹配条件的记录

例如：Table2第一行仅用col1=1和col2='a'关联Table1，统计得匹配记录数为2，与该行count_of_records一致，因此需删除Table1中所有满足col1=1 AND col2='a'的记录。

SQL动态实现方案（SQL Server环境） #

以下方案通过动态SQL遍历Table2的每一行，自动生成匹配条件并执行删除逻辑：

DECLARE @RowNum INT, @MaxRowNum INT, @Col1 INT, @Col2 VARCHAR(10), @Col3 VARCHAR(10), @Col4 VARCHAR(10), @Col5 VARCHAR(10), @Count INT
DECLARE @WhereClause NVARCHAR(MAX), @DeleteSQL NVARCHAR(MAX), @MatchCount INT

-- 获取Table2的最大行号
SELECT @MaxRowNum = MAX(RowNumber) FROM Table2

SET @RowNum = 1
WHILE @RowNum <= @MaxRowNum
BEGIN
    -- 读取当前行的字段值和计数
    SELECT 
        @Col1 = col1, 
        @Col2 = col2, 
        @Col3 = col3, 
        @Col4 = col4, 
        @Col5 = col5, 
        @Count = count_of_records
    FROM Table2 WHERE RowNumber = @RowNum

    -- 动态构建WHERE子句：仅包含非空字段的匹配条件
    SET @WhereClause = ''
    IF @Col1 IS NOT NULL SET @WhereClause += CASE WHEN @WhereClause <> '' THEN ' AND ' ELSE '' END + 't1.Col1 = ' + CAST(@Col1 AS VARCHAR(10))
    IF @Col2 IS NOT NULL SET @WhereClause += CASE WHEN @WhereClause <> '' THEN ' AND ' ELSE '' END + 't1.Col2 = ''' + @Col2 + ''''
    IF @Col3 IS NOT NULL SET @WhereClause += CASE WHEN @WhereClause <> '' THEN ' AND ' ELSE '' END + 't1.Col3 = ''' + @Col3 + ''''
    IF @Col4 IS NOT NULL SET @WhereClause += CASE WHEN @WhereClause <> '' THEN ' AND ' ELSE '' END + 't1.Col4 = ''' + @Col4 + ''''
    IF @Col5 IS NOT NULL SET @WhereClause += CASE WHEN @WhereClause <> '' THEN ' AND ' ELSE '' END + 't1.Col5 = ''' + @Col5 + ''''

    -- 统计Table1中匹配的记录数
    IF @WhereClause <> ''
    BEGIN
        SET @MatchCount = 0
        EXEC sp_executesql N'SELECT @MatchCount = COUNT(*) FROM Table1 t1 WHERE ' + @WhereClause, N'@MatchCount INT OUTPUT', @MatchCount = @MatchCount OUTPUT

        -- 如果匹配数与Table2的计数一致，则执行删除
        IF @MatchCount = @Count
        BEGIN
            SET @DeleteSQL = 'DELETE FROM Table1 WHERE ' + @WhereClause
            PRINT '执行删除语句：' + @DeleteSQL
            EXEC sp_executesql @DeleteSQL
        END
        ELSE
        BEGIN
            PRINT 'RowNumber ' + CAST(@RowNum AS VARCHAR(10)) + '：匹配数(' + CAST(@MatchCount AS VARCHAR(10)) + ')与预期计数(' + CAST(@Count AS VARCHAR(10)) + ')不一致，跳过删除'
        END
    END
    ELSE
    BEGIN
        PRINT 'RowNumber ' + CAST(@RowNum AS VARCHAR(10)) + '：所有关联字段均为空，无法匹配，跳过'
    END

    SET @RowNum = @RowNum + 1
END

代码说明 #

1. 遍历Table2行：通过WHILE循环逐行处理Table2的记录
2. 动态构建条件：根据当前行的非空字段生成对应的WHERE匹配条件，避免空字段参与关联
3. 匹配数校验：先统计Table1中符合条件的记录数，与Table2的count_of_records对比
4. 执行删除：仅当校验通过时，执行动态生成的DELETE语句
5. 日志输出：通过PRINT语句输出执行状态，便于排查问题

SSIS实现思路 #

若使用SSIS实现，可按以下步骤操作：

• Foreach循环容器：遍历Table2的每一行记录，将当前行的字段值存入变量
• 动态关联条件：通过变量表达式构建Table1与当前Table2行的关联规则（仅非空字段参与匹配）
• 查找与聚合：在数据流任务中，用查找组件匹配Table1数据，再通过聚合组件统计匹配记录数
• 条件判断：将统计数与Table2的count_of_records变量对比，仅当相等时，将匹配的Table1记录ID传入"OLE DB命令"组件执行删除

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