LINQ to SQL使用group by和take进行分组

Question

我有一个表格，长这样：

Id GroupId Value

这个表大约有100行

如何返回值的前10行，但不重复GroupId？

- Taho

Answer 1

这应该可以解决问题：

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);

编辑: 修改为返回整个对象。

- Kirk Woll

---

这个的性能如何？这会生成一个纯SQL解决方案，对吧？我只是不想返回整个表，然后在C#中处理结果。 - Taho

请注意，这只返回值列表，而不是完整的行数据。我认为他想要顶部值的完整行。 - Anthony Pegram

@Taho，好的，修改答案以返回整行。 - Kirk Woll

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 进行分组，然后从每个组中选择具有最高值的 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条记录。

- Anthony Pegram

Answer 3

这个可以获取整行的数值（我使用下面展示的样本数据可以正常工作）：

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

- Leniel Maccaferri