使用行减法将两个表合并为一个表

本文关键字：两个 合并 一个 | 更新日期: 2023-09-27 18:27:22

请帮助我为以下条件框定linq：

tbl1：

ID | Total
1     20.00
2     90.00
3    130.00

tbl2:

ID | Total
1     10.00
2     30.00

差异表：（tbl1-tbl2）

ID | Total
1     10.00
2     60.00
3    130.00

请让我知道如何把这个框起来。

使用行减法将两个表合并为一个表

在SQL世界中，取tbl1和union all tbl2的总和为负，然后取group by和id的总和值

var query = from x in tbl1.Concat(tbl2.Select(t => new { ID = t.ID, Total = -t.Total }))
            group x by x.ID into x
            select new
            {
              ID = x.Key,
              Total = x.Sum(y => y.Total)
            };

这基本上是一个完整的外部联接，可以表示为左外部联接和右外部联接的并集。

class DataModel
{
    public int ID { get; set; }
    public decimal Total { get; set; }
    public override string ToString() { return string.Format("{0}:{1}", ID, Total); }
    public override int GetHashCode() { return ID; }
    public override bool Equals(object other)
    {
        DataModel otherModel = other as DataModel;
        if (otherModel == null) return false;
        return ID == otherModel.ID;
    }
}
IEnumerable<DataModel> table1 = new List<DataModel> {
    new DataModel { ID = 1, Total = 20m },
    new DataModel { ID = 2, Total = 90m },
    new DataModel { ID = 3, Total = 130m }
};
IEnumerable<DataModel> table2 = new List<DataModel> {
    new DataModel { ID = 1, Total = 10m },
    new DataModel { ID = 2, Total = 30m },
    new DataModel { ID = 4, Total = 15m }
};
IEnumerable<DataModel> leftOuterJoin = from one in table1
    join two in table2 on one.ID equals two.ID into temp
    from right in temp.DefaultIfEmpty()
    select new DataModel {
        ID = one.ID,
        Total = one.Total - (right == null ? 0m : right.Total)
    };
IEnumerable<DataModel> rightOuterJoin = from two in table2
    join one in table1 on two.ID equals one.ID into temp
    from left in temp.DefaultIfEmpty()
    select new DataModel {
        ID = two.ID,
        Total = (left == null ? 0m : left.Total) - two.Total
    };
IEnumerable<DataModel> difference = leftOuterJoin.Union(rightOuterJoin);
foreach (DataModel item in difference)
{
    Console.WriteLine(item);
}

控制台输出为：

1:10
2:60
3:130
4:-15

查看小提琴

相关文章：