如何使用LINQ从DISTINCT行求和 [英] How to SUM the value from DISTINCT row using LINQ
本文介绍了如何使用LINQ从DISTINCT行求和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
数据表如下:
Item CartonID Quantity
A 0001 1000
A 0002 500
A 0003 250
A 0002 500
B 0002 500
B 0003 250
我的输出应该是这样的:
My output suppose to be this:
ItemNo CartonID TotalCarton TotalQuantity
A 0001,0002,0003 3 2250
B 0002,0003 2 750
但是我的结果是下面的列表:
But my result is list as below:
ItemNo CartonID TotalCarton TotalQuantity
A 0001, 0002, 0003 3 2,250
B 0002, 0003 2 750
我的代码如下:
var items = ( from item in dtTest.AsEnumerable()
group item by new
{
Item_No=item.Field<string>("Item")
}
into g
select new
{
g.Key.Item_No,
TotalCarton = (from p in dtTest.AsEnumerable()
where p.Field<string>("Item") == g.Key.Item_No
select p.Field<string>("CartonID")).Distinct().ToArray().Count(),
Total_Quantity =g.Sum((p=>p.Field<decimal>("Quantity"))).ToString("###,###,###,###"),
CartonID = (from p in dtTest.AsEnumerable()
where p.Field<string>("Item") == g.Key.Item_No
select p.Field<string>("CartonID")).Distinct().ToArray().Aggregate
((p1, p2) => p1 + ", " + p2)
}).ToList();
任何人都可以告诉我如何将具有不同纸箱ID的总数相加.预先感谢.
Anyone can tell me how to sum the total with distinct Carton ID. Thanks in advance.
推荐答案
您应该对CartonID进行分组,然后对分组元素求和:
You should GroupBy CartonID and then sum the groups elements:
类似的东西
dtTest.GroupBy(x => x.CartonId)
.ToDictionary(y => y.Key, y => y.Sum(z => z.Quantity);
这将返回一个字典,其中包含每个CartonID的总量.我希望我正确理解了这就是您想要的.
This will return a dictionary containing the total quantity per each CartonID. I hope i understood correctly that this is what you want.
根据您所说的应该检索的输出,我将查询调整为:
Based on the Output that you said is supposed to be retrieved, i adapted the query to this:
dtTest.GroupBy(x => x.ItemNo)
.Select(itemGroup => new {
itemGroup.Key,
itemGroup.Distinct(item => item.CartonID),
itemGroup.Distinct(item => item.CartonID).Count(),
itemGroup.Sum(item => item.Quantity)
});
这篇关于如何使用LINQ从DISTINCT行求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文