如何使用LINQ分组并筛选每个玩家的最高Count记录？

我正难以编写一条LINQ语句，以获取每个玩家对应最大Count值的单条记录。我的目标是显示以下2条记录：

我已通过query1将标签扁平化，再用query2统计出玩家与标签的分组Count值。请问是否可以直接筛选出每个玩家的Top记录？

void Main()
{
    Player p1 = new Player { Name = "Player1" };
    Player p2 = new Player { Name = "Player2" };
    List<PlayerAnswerRecord> UsedWords = new List<PlayerAnswerRecord>();
    UsedWords.Insert(0, new PlayerAnswerRecord { Word = new DictionaryEntry{ Word = "fruit", Tags = new List<string>{"apple", "orange"} }, Player = p1, Score = 10, RecordType = AnserRecordType.Valid });
    UsedWords.Insert(0, new PlayerAnswerRecord { Word = new DictionaryEntry{ Word = "fruit2", Tags = new List<string>{"pineapple", "orange"} }, Player = p1, Score = 10, RecordType = AnserRecordType.Valid });
    UsedWords.Insert(0, new PlayerAnswerRecord { Word = new DictionaryEntry{ Word = "fruity", Tags = new List<string>{"apple", "orange"} }, Player = p2, Score = 25, RecordType = AnserRecordType.Valid });
    
    foreach(var w in UsedWords)
    {
        //Console.WriteLine(w.Word.Word);
    }
    
    //UsedWords.GroupBy(
    var grouped =
        from entry in UsedWords
        group entry by entry.Player into newGroup
        select newGroup;
    
    //Console.Write(grouped);
    
    var query = UsedWords
        .Select(x=> new {x.Player, x.Word})
        .SelectMany(x=> x.Word.Tags);
        
    var query1 = UsedWords.SelectMany (
      c => c.Word.Tags, 
      (c, o) => 
         new  
         {
            Player = c.Player.Name, 
            Tag = o
         }
   ); 
    //var query2 = query1.GroupBy(x=>x.Name).Select(group => new {Metric = group.Key, Count = group.Count()});  
    var query2 = query1.GroupBy(x=> new {x.Player, x.Tag}).Select(group => new {Player = group.Key.Player, Tag = group.Key.Tag, Count = group.Count()}).OrderByDescending(x=>x.Count);
    //var query2 = query1.GroupBy(x=>x.Name).Select(group => group, (group, o) => new {Metric = group.Key, Count = group.Count()});
    var query3 = query2.Max(x => x.Count);
    Console.Write(query1);
    Console.Write(query2);
    Console.Write(query3);
}

// You can define other methods, fields, classes and namespaces here

public class PlayerAnswerRecord
{
    public Player Player;
    public DictionaryEntry Word;
    public int Score;
    public float ElapsedTime;
    public AnserRecordType RecordType;
}

public enum AnserRecordType
{
    Valid,
    Invalid,
    OutOfTime,
    UsedWord
}

public class DictionaryEntry
{
    public string Word;
    public List<string> Tags;
}

public class Player
{
    public string Name;
}

解决方案 #

可以基于你已有的query2，通过二次分组+排序取首条的方式直接筛选出每个玩家的Top记录，代码如下：

// 在query2之后添加以下代码获取结果
var result = query2
    .GroupBy(x => x.Player) // 按玩家分组
    .Select(g => g.OrderByDescending(x => x.Count).FirstOrDefault()); // 每组内按Count降序，取第一条

将这段整合到你的代码中，完整可运行的Main方法示例：

void Main()
{
    Player p1 = new Player { Name = "Player1" };
    Player p2 = new Player { Name = "Player2" };
    List<PlayerAnswerRecord> UsedWords = new List<PlayerAnswerRecord>();
    UsedWords.Insert(0, new PlayerAnswerRecord { Word = new DictionaryEntry{ Word = "fruit", Tags = new List<string>{"apple", "orange"} }, Player = p1, Score = 10, RecordType = AnserRecordType.Valid });
    UsedWords.Insert(0, new PlayerAnswerRecord { Word = new DictionaryEntry{ Word = "fruit2", Tags = new List<string>{"pineapple", "orange"} }, Player = p1, Score = 10, RecordType = AnserRecordType.Valid });
    UsedWords.Insert(0, new PlayerAnswerRecord { Word = new DictionaryEntry{ Word = "fruity", Tags = new List<string>{"apple", "orange"} }, Player = p2, Score = 25, RecordType = AnserRecordType.Valid });
    
    var query1 = UsedWords.SelectMany (
      c => c.Word.Tags, 
      (c, o) => 
         new  
         {
            Player = c.Player.Name, 
            Tag = o
         }
   ); 
    
    var query2 = query1.GroupBy(x=> new {x.Player, x.Tag})
                       .Select(group => new {Player = group.Key.Player, Tag = group.Key.Tag, Count = group.Count()});
    
    // 获取每个玩家的Top记录
    var result = query2
        .GroupBy(x => x.Player)
        .Select(g => g.OrderByDescending(x => x.Count).FirstOrDefault());
    
    // 输出目标结果
    foreach(var item in result)
    {
        Console.WriteLine($"Player: {item.Player}, Tag: {item.Tag}, Count: {item.Count}");
    }
}

说明 #

1. 先通过query1扁平化标签，query2统计每个玩家-标签组合的Count值
2. 按玩家分组后，对每组内的记录按Count降序排序，取第一条即为该玩家Count最大的标签记录
3. 如果存在多个标签Count相同的情况，可以添加额外排序条件（比如按标签名称升序）后再取首条，避免结果随机

内容的提问来源于stack exchange，提问作者danewfie