LINQ查询与分组和排名 | LINQ查询与分组和排名

本文介绍了LINQ查询与分组和排名的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个SQL表调用数据预测如下

表中的每一行代表一个足球比赛，每周

$ b $因此几b

我需要的结果是

 每周最佳球员次数赢
选中2 
皮特1

所以马克在2011011周和201103有多数分，皮特在201102周

获得的点的总数每播放机每周是容易的。但我不能工作，如何取最高从周群，并获得了结果，我所需要的。

我使用RANK函数，然后让这一切都在SQL查询选择所有谁拥有等级1。这是一件好事，因为如果两个球员有他们都得到正确计算每周得分相同的球员。但我想一个LINQ版本，因为它是凉爽，适合我的网站的返工！

任何帮助，将不胜感激。

好吧，我已经远远这是每一个球员，每星期相加得到的数据这一点。我现在需要做的就是挑选每个星期顶部条目，并指望它反对FootballPredictions $球员

 （上接第b $ b，其中p.FootballMatches.WeekNum< = 201101&放大器;&安培;！p.Points = NULL 
组通过新的{p.FootballMatches.WeekNum，p.Player}为G 
 p排序依据摹.Key.WeekNum，g.Sum（p => p.Points）降
选择新的
 {
 WEEKNUM = g.Key.WeekNum，
球员= g.Key .Player，
点= g.Sum（p => p.Points），
}）

捐赠

  WEEKNUM玩家点数
 201033 ranteld 26 LT; --- 1点
 201033 nicolap 25 
 201033马克25 
 201033 1969年优胜者杯25 
 201033 footysi 24 
 201033布伦丹·22 
 201033蒙蒂22 
 201033桑德拉·菲利普斯21 
 201033 SPB 20 
 201033约翰·波尔顿20 
 201033 RobBrown 19 
 201033史蒂夫·加德纳17 
 201033尼克16 
 201033 MikeSpeke 15 
 201034桑德拉·菲利普斯32 LT; --- 1点
 201034史蒂夫·加德纳27 
 201034 ranteld 25 
 201034约翰·波尔顿23 
 201034 footysi 23 
 201034马克17 
 201034 nicolap 13 
 201034布伦丹·13 
 201035布伦丹34  - ; --- 1点
 201035桑德拉·菲利普斯34  - ; --- 1点
 201035 nicolap 31 
 201035 1969年优胜者杯25 
 201035 MikeSpeke 24 
 201035史蒂夫·加德纳22 
 201035马克20 
 201035 ranteld 20 
 201035足球房地美16

所以从这个表中的真正的答案是

 玩家赢得
桑德拉飞利浦2 
布伦丹1 
 ranteld 1

。希望澄清

解决方案

这是有点令人迷惑地看到，您的查询似乎没有对应的数据。所以代替，这将是单独基于所述的数据。查询应产生有效的SQL，所以你将不必使用LINQ到对象。你可以把它适应几乎不做修改您的表。

  VAR的查询=从pred的预测中的
组pred.Points通过pred.WeekNum到本周
在新的预测
加入强的松{WEEKNUM = week.Key，积分= week.Max（）} 
等于新的{pred.WeekNum，pred.Points} 
组由1到pred.Player玩家
让胜利= player.Count（）
排序依据胜率下降，player.Key 
选择新的
 {
玩家= player.Key，
赢得，
};

I have a sql table called predictions with data as below

Week    Player     Points
201101  Mark       7
201101  Mark       7
201101  Pete       7
201101  Pete       3
201101  Mark       5
201102  Mark       2
201102  Mark       3
201102  Pete       7
201102  Pete       5
201102  Mark       5
201103  Mark       7
201103  Mark       7
201103  Pete       7
201103  Pete       3
201103  Mark       5

Each row in the table represents a football match, hence several per week

The result I need is

Player    Count of Weekly wins
Mark      2
Pete      1

So Mark had the most points in week 2011011 and 201103, Pete had the most points in week 201102

Getting the total number of points per player per week is easy. But I cannot work out how to take the highest from that weekly group and get to the result I need.

I have this all in sql query using the RANK function and then selecting all the players who have a rank of 1. This is good because if two players have the same score for a week they both get counted correctly. But I want a LINQ version because it is cool and fits my reworking of a web site!

Any help would be appreciated.

OK I have got this far which is summing the data for each player for each week. What i now need to do is pick the top entry for each week and count it against the player

(from p in FootballPredictions
        where p.FootballMatches.WeekNum <= 201101 && p.Points != null
        group p by new { p.FootballMatches.WeekNum, p.Player } into g
        orderby g.Key.WeekNum, g.Sum(p => p.Points) descending
        select new
        {
            WeekNum = g.Key.WeekNum,
            Player = g.Key.Player,
            Points = g.Sum(p => p.Points),
        })

Giving

WeekNum Player           Points
201033  ranteld           26           <--- 1 point
201033  nicolap           25
201033  Mark              25
201033  1969 cup winners  25
201033  footysi           24
201033  Brendan           22
201033  monty             22
201033  Sandra Phillips   21
201033  SPB               20
201033  John Poulton      20
201033  RobBrown          19
201033  Steve Gardner     17
201033  Nick              16
201033  MikeSpeke         15
201034  Sandra Phillips   32           <--- 1 point
201034  Steve Gardner     27
201034  ranteld           25
201034  John Poulton      23
201034  footysi           23
201034  Mark              17
201034  nicolap           13
201034  Brendan           13
201035  Brendan           34           <--- 1 point
201035  Sandra Phillips   34           <--- 1 point
201035  nicolap           31
201035  1969 cup winners  25
201035  MikeSpeke         24
201035  Steve Gardner     22
201035  Mark              20
201035  ranteld           20
201035  Football Freddie  16

So the real answer from this table is

Player             Wins
Sandra Philips      2
Brendan             1
ranteld             1

Hope that clarifies

解决方案

It was somewhat confusing to see that your query didn't seem to correspond to the data. So instead, this will be based on the data alone. The query should produce valid SQL so you won't have to use LINQ to Objects. You can adapt it to your tables with little modification.

var query = from pred in Predictions
            group pred.Points by pred.WeekNum into week
            join pred in Predictions
                on new { WeekNum = week.Key, Points = week.Max() }
                equals new { pred.WeekNum, pred.Points }
            group 1 by pred.Player into player
            let Wins = player.Count()
            orderby Wins descending, player.Key
            select new
            {
                Player = player.Key,
                Wins,
            };

这篇关于LINQ查询与分组和排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！