SQL Server 2008两表列级数据差异百分比计算方案求助

我来给你梳理几个高效的解决方案，优先用SQL来实现（毕竟直接在数据库层面处理速度最快），也给你补充C#的实现思路：

1. 有唯一关联键的场景（比如两表有相同主键ID） #

如果表A和表B有对应的唯一标识（比如主键ID），可以直接通过主键关联逐行比较。这里要注意float类型不能直接用=判断（因为是近似值），得用精度阈值（比如1e-6，你可以根据业务调整）；varchar直接比较即可，还要额外处理NULL的情况：

-- 先获取总行数（避免重复计算）
DECLARE @TotalRows INT;
SELECT @TotalRows = COUNT(*) FROM TableA;

-- 逐列统计差异率
SELECT
    'Column1' AS ColumnName,
    ROUND((COUNT(CASE WHEN 
        (A.Column1 IS NULL AND B.Column1 IS NOT NULL) 
        OR (A.Column1 IS NOT NULL AND B.Column1 IS NULL)
        OR (CASE WHEN A.Column1 IS NOT NULL AND B.Column1 IS NOT NULL 
            THEN ABS(A.Column1 - B.Column1) > 1e-6 ELSE 0 END)
    THEN 1 END) * 100.0 / @TotalRows, 2) AS DifferencePercentage
FROM TableA A
JOIN TableB B ON A.ID = B.ID

UNION ALL

SELECT
    'Column2' AS ColumnName,
    ROUND((COUNT(CASE WHEN 
        (A.Column2 IS NULL AND B.Column2 IS NOT NULL) 
        OR (A.Column2 IS NOT NULL AND B.Column2 IS NULL)
        OR (A.Column2 <> B.Column2)
    THEN 1 END) * 100.0 / @TotalRows, 2) AS DifferencePercentage
FROM TableA A
JOIN TableB B ON A.ID = B.ID

-- 剩下的18列照着上面的格式复制替换列名即可

2. 无关联键但行顺序完全一致的场景 #

如果两表没有主键，但数据是按相同顺序插入的，可以用ROW_NUMBER()生成行号来关联：

DECLARE @TotalRows INT;
SELECT @TotalRows = COUNT(*) FROM TableA;

WITH A_Row AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM TableA
),
B_Row AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM TableB
)
SELECT
    'Column1' AS ColumnName,
    ROUND((COUNT(CASE WHEN 
        (A.Column1 IS NULL AND B.Column1 IS NOT NULL) 
        OR (A.Column1 IS NOT NULL AND B.Column1 IS NULL)
        OR (CASE WHEN A.Column1 IS NOT NULL AND B.Column1 IS NOT NULL 
            THEN ABS(A.Column1 - B.Column1) > 1e-6 ELSE 0 END)
    THEN 1 END) * 100.0 / @TotalRows, 2) AS DifferencePercentage
FROM A_Row A
JOIN B_Row B ON A.RowNum = B.RowNum

UNION ALL

-- 其他列同理...

3. 动态SQL自动生成（推荐！20列不用手动写） #

手动写20列太费时间，用动态SQL自动读取表结构生成统计语句，省事儿还不容易错：

DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @TotalRows INT;
SELECT @TotalRows = COUNT(*) FROM TableA;

-- 生成每列的统计语句
SELECT @SQL = @SQL + '
UNION ALL
SELECT
    ''' + COLUMN_NAME + ''' AS ColumnName,
    ROUND((COUNT(CASE WHEN 
        (A.' + COLUMN_NAME + ' IS NULL AND B.' + COLUMN_NAME + ' IS NOT NULL) 
        OR (A.' + COLUMN_NAME + ' IS NOT NULL AND B.' + COLUMN_NAME + ' IS NULL)'
        + CASE WHEN DATA_TYPE = 'float' THEN 
        ' OR (CASE WHEN A.' + COLUMN_NAME + ' IS NOT NULL AND B.' + COLUMN_NAME + ' IS NOT NULL THEN ABS(A.' + COLUMN_NAME + ' - B.' + COLUMN_NAME + ') > 1e-6 ELSE 0 END)'
        ELSE 
        ' OR (A.' + COLUMN_NAME + ' <> B.' + COLUMN_NAME + ')'
        END + '
    THEN 1 END) * 100.0 / ' + CAST(@TotalRows AS VARCHAR) + ', 2) AS DifferencePercentage
FROM TableA A
JOIN TableB B ON A.ID = B.ID' -- 这里如果是行号关联，替换成上面的CTE关联逻辑
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableA' -- 表A和表B列结构一致，所以读表A的列即可

-- 去掉开头的UNION ALL
SET @SQL = STUFF(@SQL, 1, 10, '');

-- 执行动态SQL
EXEC sp_executesql @SQL;

注意：如果是行号关联的场景，把上面的JOIN TableB B ON A.ID = B.ID替换成CTE的关联逻辑就行。

如果需要在应用层处理，C#的思路是读取两表数据，逐行逐列比较，统计差异：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

class TableComparer
{
    static void Main()
    {
        string connectionString = "Your SQL Server Connection String";
        string tableA = "TableA";
        string tableB = "TableB";
        float floatThreshold = 1e-6f; // 浮点精度阈值，可根据业务调整

        // 获取两表数据
        DataTable dtA = GetTableData(connectionString, tableA);
        DataTable dtB = GetTableData(connectionString, tableB);

        if (dtA.Rows.Count != dtB.Rows.Count)
        {
            Console.WriteLine("两表行数不一致，无法比较！");
            return;
        }

        int totalRows = dtA.Rows.Count;
        Dictionary<string, int> columnDiffCounts = new Dictionary<string, int>();

        // 初始化每个列的差异计数为0
        foreach (DataColumn col in dtA.Columns)
        {
            columnDiffCounts[col.ColumnName] = 0;
        }

        // 逐行逐列比较
        for (int i = 0; i < totalRows; i++)
        {
            DataRow rowA = dtA.Rows[i];
            DataRow rowB = dtB.Rows[i];

            foreach (DataColumn col in dtA.Columns)
            {
                object valA = rowA[col];
                object valB = rowB[col];

                bool isDiff = false;

                // 处理NULL情况
                if (valA == DBNull.Value && valB != DBNull.Value)
                    isDiff = true;
                else if (valA != DBNull.Value && valB == DBNull.Value)
                    isDiff = true;
                else if (valA != DBNull.Value && valB != DBNull.Value)
                {
                    // 根据列类型处理
                    if (col.DataType == typeof(float))
                    {
                        float floatA = (float)valA;
                        float floatB = (float)valB;
                        if (Math.Abs(floatA - floatB) > floatThreshold)
                            isDiff = true;
                    }
                    else if (col.DataType == typeof(string))
                    {
                        string strA = (string)valA;
                        string strB = (string)valB;
                        // 这里可以根据需求决定是否区分大小写，比如用StringComparison.OrdinalIgnoreCase
                        if (!string.Equals(strA, strB, StringComparison.Ordinal))
                            isDiff = true;
                    }
                    // 其他数据类型可以继续扩展，比如int直接比较等
                    else
                    {
                        if (!valA.Equals(valB))
                            isDiff = true;
                    }
                }

                if (isDiff)
                    columnDiffCounts[col.ColumnName]++;
            }
        }

        // 输出差异百分比
        Console.WriteLine("列名\t差异百分比");
        foreach (var kvp in columnDiffCounts)
        {
            float percentage = (kvp.Value * 100.0f) / totalRows;
            Console.WriteLine($"{kvp.Key}\t{Math.Round(percentage, 2)}%");
        }
    }

    static DataTable GetTableData(string connStr, string tableName)
    {
        DataTable dt = new DataTable();
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            string sql = $"SELECT * FROM {tableName}";
            using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
            {
                da.Fill(dt);
            }
        }
        return dt;
    }
}

如果数据量很大（比如百万级），建议用SqlDataReader逐行读取，不要一次性加载整个表到DataTable，能节省不少内存。

内容的提问来源于stack exchange，提问作者zain ul abidin