LINQ to SQL group by 和 take

Question

我有一个看起来像这样的表：

Id GroupId Value

它有大约 100 行

如何返回值的前 10 行但没有重复的 GroupId？

Answer 1

这将获得完整的行值（它对我来说适用于下面显示的示例数据）：

static void Main(string[] args)
{ 
    Whatever one = new Whatever() {GroupId = 1, Id = 1, Value = 2};
    Whatever two = new Whatever() { GroupId = 1, Id = 2, Value = 8 };
    Whatever three = new Whatever() { GroupId = 2, Id = 3, Value = 16 };
    Whatever four = new Whatever() { GroupId = 2, Id = 4, Value = 7 };
    Whatever five = new Whatever() { GroupId = 3, Id = 5, Value = 21 };
    Whatever six = new Whatever() { GroupId = 3, Id = 6, Value = 12 };
    Whatever seven = new Whatever() { GroupId = 4, Id = 7, Value = 5 };
    Whatever eight = new Whatever() { GroupId = 5, Id = 8, Value = 17 };
    Whatever nine = new Whatever() { GroupId = 6, Id = 9, Value = 13 };
    Whatever ten = new Whatever() { GroupId = 7, Id = 10, Value = 44 };

    List<Whatever> list = new List<Whatever>();
    list.Add(one);
    list.Add(two);
    list.Add(three);
    list.Add(four);
    list.Add(five);
    list.Add(six);
    list.Add(seven);
    list.Add(eight);
    list.Add(nine);
    list.Add(ten);

    var results = (from w in list
                   group w by w.GroupId into g
                   select new { GroupId = g.Key,
                                Value = g.Max(w => w.Value),
                                Id = g.OrderBy(w=>w.Value).Last().Id }).
                   OrderByDescending(w=>w.Value).Take(5);

    foreach (var r in results)
    {
        Console.WriteLine("GroupId = {0},
                           Id = {1},
                           Value = {2}",
                           r.GroupId, r.Id,  r.Value);
    }

}

输出：

GroupId = 7, Id = 10, Value = 44
GroupId = 3, Id = 5, Value = 21
GroupId = 5, Id = 8, Value = 17
GroupId = 2, Id = 3, Value = 16
GroupId = 6, Id = 9, Value = 13

Answer 2

不确定这是否可以转换为 LINQ-to-SQL，但这是 L2Obj 的一个想法。

var query = (from foo in foos
                group foo by foo.GroupId into fg
                select fg.OrderByDescending(f => f.Value).First())
                .OrderByDescending(f => f.Value)
                .Take(10);

用英语来说，它对 GroupId 进行分组，然后从每个组中选择具有最高 Value 的 Foo，对它们进行排序，然后取 10。如果有的话，您可以从 L2SQL 获取对象的具体列表，然后在内存中执行分组，这不应该是性能/内存问题，因为您说只有 100 行。

对于 LINQ-to-SQL，您可能尝试这样的操作。

var sqlQuery = (from foo in foos
                join y in
                    (from f2 in foos
                        join x in
                            (from f1 in foos
                            group f1 by f1.GroupId into vg
                            select new { GroupId = vg.Key, MaxVal = vg.Max(f => f.Value) })
                            on f2.GroupId equals x.GroupId
                        where f2.Value == x.MaxVal
                        group f2 by f2.GroupId into mg
                        select new { GroupId = mg.Key, MinId = mg.Min(f => f.Id) })
                on foo.Id equals y.MinId
                orderby foo.Value descending
                select foo).Take(10);

这是基于 SQL 查询来执行相同的操作，

Select top 10 f.*
From Foos f
Inner Join 
(Select f.GroupID, min(f.Id) as MinId
From Foos f
Inner Join
(Select GroupId, Max(Value) as MaxVal
From Foos
Group By GroupId) x
on f.GroupId = x.GroupId 
and f.Value = x.MaxVal
Group By f.GroupId) y
on f.Id = y.MinId
order by f.Value desc

它基本上执行两个分组。第一个获取每个组的最大值，第二个获取每个组中具有最大值的每条记录的最小 ID（如果一组中的 2 条记录具有相同的值），然后选择前 10 条记录。

Answer 3

这应该可以做到：

var results = table
    .GroupBy(x => x.GroupId)
    .Select(x => new { Row = x, Value = x.Max(y => y.Value) })
    .OrderByDescending(x => x.Value)
    .Select(x => x.Row)
    .Take(10);

编辑：修改以返回整个对象。