C#开发中如何对比两张SQL表对应列计算积分字段

你可以根据自己的技术选型选两种实现路径，要么在数据库查询阶段直接完成得分计算，C#端直接拿最终结果；要么把两张表数据加载到C#内存后通过关联逻辑计算，两种方式的可运行代码如下：

方案1：SQL层直接计算（推荐，性能更优） #

直接通过队名字段关联两张表，用CASE WHEN判断得分是否一致，直接返回计算好的分值，C#端不需要额外写业务判断逻辑。
如果你的表字段就是示例中带空格的命名，SQL里记得用方括号转义字段名：

SELECT 
    p.[Prediction Home team] AS HomeTeam,
    p.[Prediction Home Score] AS PredictHomeScore,
    r.[Actual Home Score] AS ActualHomeScore,
    CASE WHEN p.[Prediction Home Score] = r.[Actual Home Score] THEN 5 ELSE 0 END AS CalcPoint
FROM Prediction p
INNER JOIN Result r 
    ON p.[Prediction Home team] = r.[Actual Home team]

C#端用原生SqlClient调用查询的示例代码：

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

// 替换为你自己的数据库连接字符串
const string connectionString = "Server=你的数据库地址;Database=你的库名;Uid=账号;Pwd=密码;";
var calcResults = new List<ScoreCalcDto>();

using (var conn = new SqlConnection(connectionString))
{
    const string sql = @"
        SELECT 
            p.[Prediction Home team] AS HomeTeam,
            p.[Prediction Home Score] AS PredictHomeScore,
            r.[Actual Home Score] AS ActualHomeScore,
            CASE WHEN p.[Prediction Home Score] = r.[Actual Home Score] THEN 5 ELSE 0 END AS CalcPoint
        FROM Prediction p
        INNER JOIN Result r 
            ON p.[Prediction Home team] = r.[Actual Home team]
    ";
    var cmd = new SqlCommand(sql, conn);
    conn.Open();
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        calcResults.Add(new ScoreCalcDto
        {
            HomeTeam = reader.GetString(0),
            PredictHomeScore = reader.GetInt32(1),
            ActualHomeScore = reader.GetInt32(2),
            CalcPoint = reader.GetInt32(3)
        });
    }
}

// 接收计算结果的实体类
public class ScoreCalcDto
{
    public string HomeTeam { get; set; }
    public int PredictHomeScore { get; set; }
    public int ActualHomeScore { get; set; }
    public int CalcPoint { get; set; }
}

方案2：C#内存中通过LINQ关联计算 #

如果你已经把两张表的全量数据加载到C#集合对象中，可以直接用LINQ的Join方法关联计算，不需要额外写SQL：

using System.Linq;
using System.Collections.Generic;

// 假设这两个集合是你已经从数据库查出来的表数据
List<PredictionRecord> predictionRecords = new List<PredictionRecord>();
List<ResultRecord> resultRecords = new List<ResultRecord>();

// 关联计算得分
var calcResults = predictionRecords.Join(
    resultRecords,
    pred => pred.HomeTeam, // 预测表关联键：主队名称
    res => res.HomeTeam, // 结果表关联键：主队名称
    (pred, res) => new ScoreCalcDto
    {
        HomeTeam = pred.HomeTeam,
        PredictHomeScore = pred.PredictHomeScore,
        ActualHomeScore = res.ActualHomeScore,
        CalcPoint = pred.PredictHomeScore == res.ActualHomeScore ? 5 : 0
    }
).ToList();

// 两张表对应的实体类定义
public class PredictionRecord
{
    public string HomeTeam { get; set; }
    public int PredictHomeScore { get; set; }
}

public class ResultRecord
{
    public string HomeTeam { get; set; }
    public int ActualHomeScore { get; set; }
}

注意事项：上面两种方案用的都是内关联逻辑，会自动过滤掉“有预测无对应结果”或“有结果无对应预测”的球队数据，如果需要保留这类不匹配数据，把关联逻辑改成左连接即可。

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