代码之家 › 专栏 › 技术社区 › nnmmss

从Datatable中选择,按多个带条件的列分组

visual-studio-2010 visual-studio c#

nnmmss · 技术社区 · 7 年前

我有一个数据表,其中有4个字段。

 ptime, Substation, ColumnTitle, ValueAmount

我想按Substation和ColumnTitle对具有最大ValueAmount的行进行分组。假设这是我的数据表

 ptime,  Substation, ColumnTitle, ValueAmount
 --------------------------------------------
 '01:00'    'AA'       'A'          100
 '03:00'    'AA'       'A'          150
 '01:00'    'BB'       'A'          10
 '02:00'    'BB'       'A'          11
 '01:00'    'AA'       'C'          77
 '02:00'    'AA'       'C'          88
 '04:00'    'AA'       'C'          99

所以我想要

   ptime,  Substation, ColumnTitle, ValueAmount
 --------------------------------------------
 '03:00'    'AA'       'A'          150
 '02:00'    'BB'       'A'          11
 '04:00'    'AA'       'C'          99

用于此目的的最佳linq命令是什么? 我已经对其进行了编程:

 var newSort = from row in dtFinal.AsEnumerable()
   group row by new  {Substation = row.Field<string>("Substation"), 
            ColumnTitle= row.Field<string>("ColumnTitle")} into grp       
   select new
   {
     Substation = grp.Key.Substation,
     ColumnTitle = grp.Key.ColumnTitle,         
     ValueAmount = grp.Max(r => decimal.Parse(r.Field<string>("ValueAmount")))
   };

但是在这个语法中,我没有这个时间。我该怎么做。

非常感谢。

2 回复 | 直到 7 年前

Tim Schmelter 7 年前

您可以按此值降序排列组以获得“MaxRow”,在那里您可以找到 ptime :

var newSort = dtFinal.AsEnumerable()
    .GroupBy(row => new
    {
        Substation = row.Field<string>("Substation"),
        ColumnTitle = row.Field<string>("ColumnTitle")
    })
    .Select(g => new
    {
        g.Key.Substation,
        g.Key.ColumnTitle,
        MaxRow = g.OrderByDescending(row => decimal.Parse(row.Field<string>("ValueAmount"))).First()
    })
    .Select(x => new 
    {
        ptime = x.MaxRow.Field<string>("ptime"), // voilÃ 
        x.Substation,
        x.ColumnTitle,
        ValueAmount = decimal.Parse(x.MaxRow.Field<string>("ValueAmount"))
    });

由于使用了查询语法,在本例中,由于 let :

var newSort = from row in dtFinal.AsEnumerable()
  let xRow = new { Row = row, valueAmount = decimal.Parse(row.Field<string>("ValueAmount")) }
  group xRow by new
  {
     Substation = row.Field<string>("Substation"),
     ColumnTitle = row.Field<string>("ColumnTitle")
  } into grp
  let maxRow = grp.OrderByDescending(x => x.valueAmount).First()
  select new
  {
     ptime = maxRow.Row.Field<string>("ptime"),
     grp.Key.Substation,
     grp.Key.ColumnTitle,
     ValueAmount = maxRow.valueAmount
  };

Mittal Patel 7 年前

这可以使用以下2个查询来实现:

var dt = from cu in DtFinals
         group cu.ValueAmount by new
            {
                cu.Substation,
                cu.ColumnTitle,
            } into gs
            select new
            {
                Substation = gs.Key.Substation,
                ColumnTitle = gs.Key.ColumnTitle,
                ValueAmount = gs.Max()
            };

var final = from c in DtFinals
            join d in dt on 
            new {c.Substation, c.ColumnTitle,c.ValueAmount} equals 
            new {d.Substation, d.ColumnTitle, d.ValueAmount}
            select new 
            { 
                ptime = c.Ptime, 
                Substation = d.Substation, 
                ColumnTitle = d.ColumnTitle,
                ValueAmount = d.ValueAmount
            };