SQL Server多表计数对账：合并Staging与DW计数至同一行

这个需求很常见，咱们可以用两种实用的方法来把分散的Staging和DW计数合并到同一行，下面给你详细拆解：

方法一：条件聚合（推荐，灵活性更高） #

这种方法先把两个表的计数结果整合到一起，再通过条件判断分别汇总Staging和DW的数值，逻辑清晰且适配各种表名映射规则。

示例代码 #

WITH CombinedCounts AS (
    -- 处理Staging表的计数，标记来源为Staging
    SELECT 
        DivID,
        TableName, -- 假设Staging表名就是业务表名（比如Customer）
        COUNT(*) AS CountValue,
        'Staging' AS Source
    FROM StagingTable
    GROUP BY DivID, TableName

    UNION ALL -- 用UNION ALL比UNION高效，因为不需要去重

    -- 处理DW表的计数，统一业务表名（比如DW表名是CustomerDW，去掉末尾的DW）
    SELECT 
        DivID,
        LEFT(TableName, LEN(TableName) - 2) AS TableName,
        COUNT(*) AS CountValue,
        'DW' AS Source
    FROM DWTable
    GROUP BY DivID, TableName
)
SELECT 
    DivID,
    TableName,
    -- 汇总Staging侧的计数，没有则显示0
    SUM(CASE WHEN Source = 'Staging' THEN CountValue ELSE 0 END) AS StagingCount,
    -- 汇总DW侧的计数，没有则显示0
    SUM(CASE WHEN Source = 'DW' THEN CountValue ELSE 0 END) AS DWCount
FROM CombinedCounts
GROUP BY DivID, TableName
ORDER BY DivID, TableName;

关键调整点 #

如果你的表名映射规则不是简单去掉DW后缀（比如Staging表是Customer_Staging，DW表是Customer），可以修改TableName的生成逻辑：

-- 适配自定义表名映射
TableName = CASE 
    WHEN Source = 'Staging' THEN REPLACE(TableName, '_Staging', '')
    ELSE TableName 
END

方法二：使用PIVOT（适合固定来源场景） #

如果你的数据来源只有Staging和DW两类，也可以用PIVOT把行数据转成列，实现合并效果。

示例代码 #

WITH CombinedCounts AS (
    SELECT 
        DivID,
        -- 统一业务表名
        CASE 
            WHEN TableName LIKE '%DW' THEN LEFT(TableName, LEN(TableName)-2)
            ELSE TableName 
        END AS TableName,
        COUNT(*) AS CountValue,
        -- 定义计数类型，对应最终的列名
        CASE 
            WHEN TableName LIKE '%DW' THEN 'DWCount'
            ELSE 'StagingCount' 
        END AS CountType
    FROM (
        -- 合并两个表的原始数据
        SELECT DivID, TableName FROM StagingTable
        UNION ALL
        SELECT DivID, TableName FROM DWTable
    ) t
    GROUP BY DivID, TableName
)
SELECT 
    DivID,
    TableName,
    -- 用ISNULL处理空值，确保没有数据时显示0
    ISNULL(StagingCount, 0) AS StagingCount,
    ISNULL(DWCount, 0) AS DWCount
FROM CombinedCounts
PIVOT (
    SUM(CountValue)
    FOR CountType IN (StagingCount, DWCount)
) p
ORDER BY DivID, TableName;

注意事项 #

• 优先用UNION ALL代替UNION，避免不必要的去重操作，提升查询效率；
• 如果某个DivID的业务表在Staging或DW中没有数据，用ISNULL或CASE把NULL转换成0，让结果更规整；
• 一定要根据实际的表名命名规则调整TableName的转换逻辑，确保Staging和DW的同业务表能匹配上。

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