基于主键longitem对比SQL Server 2014中BI与CUST表列级数据并生成报告

需求说明 #

基于主键longitem对比BI和CUST两张表的所有列数据，找出以下三类不匹配项并生成自定义格式报告：

1. 主键存在于两张表，但某列值不一致（包括NULL与非NULL的差异）
2. 主键仅存在于BI表
3. 主键仅存在于CUST表

准备工作：表结构与测试数据 #

首先定义两张表的DDL及测试数据：

-- BI表结构
CREATE TABLE BI (
    longitem BIGINT PRIMARY KEY,
    col1 VARCHAR(50),
    col2 INT,
    col3 DATETIME,
    col4 DECIMAL(18,2)
);

-- CUST表结构
CREATE TABLE CUST (
    longitem BIGINT PRIMARY KEY,
    col1 VARCHAR(50),
    col2 INT,
    col3 DATETIME,
    col4 DECIMAL(18,2)
);

-- 插入BI表测试数据
INSERT INTO BI VALUES
(1, 'A', 10, '2024-01-01', 100.50),
(2, 'B', 20, '2024-01-02', 200.75),
(3, 'C', NULL, '2024-01-03', 300.00),
(4, 'D', 40, '2024-01-04', NULL);

-- 插入CUST表测试数据
INSERT INTO CUST VALUES
(1, 'A', 10, '2024-01-01', 100.50), -- 完全匹配
(2, 'B', 25, '2024-01-02', 200.75), -- col2不匹配
(3, 'C', NULL, '2024-01-04', 300.00), -- col3不匹配
(5, 'E', 50, '2024-01-05', 500.00); -- 仅存在于CUST

可行解决方案 #

以下SQL通过FULL JOIN关联两张表，结合列转行（UNPIVOT）处理列级对比，最终生成符合要求的自定义报告：

WITH CombinedData AS (
    SELECT 
        COALESCE(BI.longitem, CUST.longitem) AS longitem,
        BI.col1 AS BI_col1,
        CUST.col1 AS CUST_col1,
        BI.col2 AS BI_col2,
        CUST.col2 AS CUST_col2,
        BI.col3 AS BI_col3,
        CUST.col3 AS CUST_col3,
        BI.col4 AS BI_col4,
        CUST.col4 AS CUST_col4,
        CASE WHEN BI.longitem IS NULL THEN '仅存在于CUST'
             WHEN CUST.longitem IS NULL THEN '仅存在于BI'
             ELSE '列值不匹配' END AS mismatch_type
    FROM BI
    FULL JOIN CUST ON BI.longitem = CUST.longitem
    -- 过滤所有存在不匹配的记录
    WHERE BI.longitem IS NULL 
       OR CUST.longitem IS NULL
       OR BI.col1 <> CUST.col1 OR (BI.col1 IS NULL AND CUST.col1 IS NOT NULL) OR (BI.col1 IS NOT NULL AND CUST.col1 IS NULL)
       OR BI.col2 <> CUST.col2 OR (BI.col2 IS NULL AND CUST.col2 IS NOT NULL) OR (BI.col2 IS NOT NULL AND CUST.col2 IS NULL)
       OR BI.col3 <> CUST.col3 OR (BI.col3 IS NULL AND CUST.col3 IS NOT NULL) OR (BI.col3 IS NOT NULL AND CUST.col3 IS NULL)
       OR BI.col4 <> CUST.col4 OR (BI.col4 IS NULL AND CUST.col4 IS NOT NULL) OR (BI.col4 IS NOT NULL AND CUST.col4 IS NULL)
),
UnpivotedMismatches AS (
    SELECT 
        longitem,
        mismatch_type,
        -- 转换列名为中文（按需调整）
        CASE col_name 
            WHEN 'col1' THEN '列1'
            WHEN 'col2' THEN '列2'
            WHEN 'col3' THEN '列3'
            WHEN 'col4' THEN '列4' END AS column_name,
        bi_value,
        cust_value
    FROM CombinedData
    -- 拆分BI表的列值
    UNPIVOT (
        bi_value FOR col_name IN (BI_col1, BI_col2, BI_col3, BI_col4)
    ) AS up_bi
    -- 拆分CUST表的列值
    UNPIVOT (
        cust_value FOR col_name IN (CUST_col1, CUST_col2, CUST_col3, CUST_col4)
    ) AS up_cust
    -- 匹配对应的列，并过滤列值不匹配的记录
    WHERE LEFT(up_bi.col_name, 3) = LEFT(up_cust.col_name, 3)
      AND (bi_value <> cust_value OR (bi_value IS NULL AND cust_value IS NOT NULL) OR (bi_value IS NOT NULL AND cust_value IS NULL))
)
-- 合并列级不匹配记录与单表存在记录
SELECT 
    longitem AS '主键值',
    column_name AS '列名',
    ISNULL(CONVERT(VARCHAR(100), bi_value), 'NULL') AS 'BI表值',
    ISNULL(CONVERT(VARCHAR(100), cust_value), 'NULL') AS 'CUST表值',
    mismatch_type AS '不匹配类型'
FROM UnpivotedMismatches
UNION ALL
SELECT 
    longitem AS '主键值',
    'N/A' AS '列名',
    -- 拼接所有列值展示单表存在的记录
    ISNULL(CONVERT(VARCHAR(100), BI_col1), 'NULL') + ', ' + 
    ISNULL(CONVERT(VARCHAR(100), BI_col2), 'NULL') + ', ' + 
    ISNULL(CONVERT(VARCHAR(100), BI_col3), 'NULL') + ', ' + 
    ISNULL(CONVERT(VARCHAR(100), BI_col4), 'NULL') AS 'BI表值',
    ISNULL(CONVERT(VARCHAR(100), CUST_col1), 'NULL') + ', ' + 
    ISNULL(CONVERT(VARCHAR(100), CUST_col2), 'NULL') + ', ' + 
    ISNULL(CONVERT(VARCHAR(100), CUST_col3), 'NULL') + ', ' + 
    ISNULL(CONVERT(VARCHAR(100), CUST_col4), 'NULL') AS 'CUST表值',
    mismatch_type AS '不匹配类型'
FROM CombinedData
WHERE mismatch_type IN ('仅存在于BI', '仅存在于CUST')
ORDER BY longitem, column_name;

执行结果说明 #

运行上述SQL后，将得到如下格式的报告（示例输出）：

注意事项 #

1. NULL值处理：SQL中NULL与任何值（包括NULL）的比较结果都为未知，因此必须单独判断NULL与非NULL的差异。
2. 数据类型转换：将不同类型的列值转换为VARCHAR时，可根据需求调整格式（比如DATETIME用CONVERT(VARCHAR(20), col, 120)生成标准时间格式）。
3. 扩展列支持：如果表中新增列，只需在CombinedData中添加对应的BI_xxx和CUST_xxx字段，并在UNPIVOT部分加入这些列即可。

内容的提问来源于stack exchange，提问作者Angel D'souza