SQL如何动态或递归比较多表列并生成客户维度校验结果

核心思路 #

• 自动从系统表抓取所有符合命名规则的同结构业务表，无需手动配置表名
• 先汇总所有表的全量CustomerID去重，作为主表的维度列
• 动态生成存在性标记字段，以及多表字段的对比逻辑，自动拼接差异描述

实现代码 #

-- 1. 定义变量存储动态SQL、表名列表
DECLARE @sql NVARCHAR(MAX) = ''
DECLARE @tableList NVARCHAR(MAX) = ''
DECLARE @existCols NVARCHAR(MAX) = ''
DECLARE @compareLogic NVARCHAR(MAX) = ''

-- 2. 抓取所有符合命名规则的同结构表（这里匹配Table_前缀的表，可根据实际规则调整）
SELECT @tableList = STRING_AGG(QUOTENAME(name), ','),
       @existCols = STRING_AGG(CAST('CASE WHEN ' + QUOTENAME(name) + '.CustomerID IS NOT NULL THEN 1 ELSE 0 END AS ' + QUOTENAME('是否存在于' + name) AS NVARCHAR(MAX)), ','),
       @compareLogic = @compareLogic + 
       (SELECT STRING_AGG(CAST(
        -- StartDate对比
        'CASE WHEN ' + QUOTENAME(t1.name) + '.StartDate ' + op + ' ' + QUOTENAME(t2.name) + '.StartDate THEN ''' + t1.name + ' StartDate ' + op + ' ' + t2.name + ' StartDate;'' ELSE '''' END + ' +
        -- EndDate对比
        'CASE WHEN ' + QUOTENAME(t1.name) + '.EndDate ' + op + ' ' + QUOTENAME(t2.name) + '.EndDate THEN ''' + t1.name + ' EndDate ' + op + ' ' + t2.name + ' EndDate;'' ELSE '''' END + '
        AS NVARCHAR(MAX)), '')
        FROM sys.tables t2
        CROSS JOIN (SELECT '<' AS op UNION SELECT '>' AS op) ops
        WHERE t2.name LIKE 'Table_%' AND t2.object_id > t1.object_id
       )
FROM sys.tables t1
WHERE t1.name LIKE 'Table_%' -- 可根据实际表命名规则调整过滤条件
AND EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = t1.object_id AND c.name IN ('CustomerID','StartDate','EndDate'))

-- 去掉末尾多余的加号
SET @compareLogic = LEFT(@compareLogic, LEN(@compareLogic) - 1)

-- 3. 拼接完整动态SQL
SET @sql = '
WITH AllCustomer AS (
    ' + (SELECT STRING_AGG(CAST('SELECT CustomerID FROM ' + QUOTENAME(name) AS NVARCHAR(MAX)), ' UNION ') FROM sys.tables WHERE name LIKE 'Table_%') + '
)
SELECT 
    a.CustomerID,
    ' + @existCols + ',
    LTRIM(RTRIM(' + @compareLogic + ')) AS 对比详情
FROM AllCustomer a
' + (SELECT STRING_AGG(CAST('LEFT JOIN ' + QUOTENAME(name) + ' ON ' + QUOTENAME(name) + '.CustomerID = a.CustomerID' AS NVARCHAR(MAX)), ' ') FROM sys.tables WHERE name LIKE 'Table_%') + '
'

-- 4. 执行动态SQL
EXEC sp_executesql @sql

效果说明 #

• 新增同结构表时，只要符合命名规则，不需要修改代码，直接执行即可自动纳入对比范围
• 输出结果包含每个客户在各表的存在标记，以及所有跨表的字段大小差异描述，和预期的输出结构一致

注意事项 #

• 如果你的表命名规则不是Table_前缀，修改代码中两处name LIKE 'Table_%'的过滤条件即可
• 如果需要新增对比字段，只要所有表都有该字段，在@compareLogic的拼接逻辑中新增对应字段的判断即可

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