如何使用LINQ从DISTINCT行求和 [英] How to SUM the value from DISTINCT row using LINQ

问题描述

数据表如下:

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屋！