如何获取两个数据表之间的差异

本文关键字：之间 数据表 两个 何获取 获取 | 更新日期: 2023-09-27 18:21:24

我正在尝试制作一个简单的财务表，将实际数字与预算数字进行比较，并显示差异。

**Actual**
ID          Revenue
1           100
2           120
**Budget**
ID          Revenue
1           120
2           100

一旦比较，它会给我这个表：

**Variance**
ID          Revenue
1           -20
2           +20

这就是我现在设置两张表的方式：

        // Make the Actual table
        DataTable allActual = new DataTable("actualTable");
        DataRow rowActual;
        // Create columns and add to the DataTable.
        allActual.Columns.Add("Title", typeof(string));
        allActual.Columns.Add("Revenue", typeof(string));
        // Add rows to the Actual grid
        foreach (SPListItem item in actualItems)
        {
            rowActual = allActual.Rows.Add();
            rowActual["Title"] = item["Title"].ToString();
            rowActual["Revenue"] = item["Revenue"].ToString();
        }

        // Make the Budget table
        DataTable allBudget = new DataTable("budgetTable");
        DataRow rowBudget;
        // Create columns and add to the DataTable.
        allBudget.Columns.Add("Title", typeof(string));
        allBudget.Columns.Add("Revenue", typeof(string));
        // Add rows to the Actual grid
        foreach (SPListItem item in budgetItems)
        {
            rowBudget = allBudget.Rows.Add();
            rowBudget["Title"] = item["Title"].ToString();
            rowBudget["Revenue"] = item["Revenue"].ToString();
        }

如果能帮我找到正确的答案，我将不胜感激。谢谢

如何获取两个数据表之间的差异

您可以使用LINQ To DataSet来获得差异：

编辑：这是Dictionary方法的C#版本（使用一个简单的ToList来获得这种匿名类型的列表）：

var diff = (from  aRow in tblActual.AsEnumerable()
            join bRow in tblBudget.AsEnumerable()
            on aRow["ID"] equals bRow["ID"]
            let aRev = (int)aRow["Revenue"]
            let bRev = (int)bRow["Revenue"]
            where aRev != bRev
            select new 
            {
                ID = (int)aRow["ID"],
                Diff = aRev - bRev
            }).ToDictionary(r => r.ID, r=> r.Diff );

VB.NET：

Dim diff = (From aRow In tblActual
            Join bRow In tblBudget _
            On aRow("ID") Equals bRow("ID")
            Let aRev = DirectCast(aRow("Revenue"), Int32)
            Let bRev = DirectCast(bRow("Revenue"), Int32)
            Where aRev <> bRev
            Select New With {.ID = DirectCast(aRow("ID"), Int32),
                            .Diff = aRev - bRev}).ToList

For Each d In diff
    Dim id = d.ID
    Dim revDifference = d.Diff
Next

或者，如果您想查找给定ID:的差异，请创建Dictionary（Of Int32，Int32）

Dim diff = (From aRow In tblActual
            Join bRow In tblBudget _
            On aRow("ID") Equals bRow("ID")
            Let aRev = DirectCast(aRow("Revenue"), Int32)
            Let bRev = DirectCast(bRow("Revenue"), Int32)
            Where aRev <> bRev
            Select New With {.ID = DirectCast(aRow("ID"), Int32),
                            .Diff = aRev - bRev}).
                            ToDictionary(Function(r) (r.ID), 
                                         Function(r) (r.Diff))
' returns diff for a given ID '
Dim diffForID2 = diff(2)
' iterate all dictionary entries '
For Each d In diff
    Dim id = d.Key
    Dim revDifference = d.Value
Next

假设实际和预算列表之间存在直接的一对一关系，为什么不在读取每个列表项时执行计算？这里有一些伪代码：

//actuals contains all the list items from the Actuals list
//budget contains all the list items from the Budget list
//variance is an array of numbers with the same length as the actuals list
for (i=0; i<actuals.length; i++)
{
 diff = actuals[i].value - budget[i].value;
 variance[i] = diff;
}
相关文章：