LINQ查询与分组和排名 [英] LINQ Query with grouping and ranking
问题描述
我有一个SQL表调用数据预测如下
周刊玩家点数
201101马克7
201101马克7
201101皮特7
201101皮特3
201101马克5
201102马克2
201102马克3
201102皮特7
201102皮特5
201102马克5
201103马克7
201103马克7
201103皮特7
201103皮特3
201103马克5
表中的每一行代表一个足球比赛，每周
$ b $因此几b
我需要的结果是
每周最佳球员次数赢
选中2
皮特1
所以马克在2011011周和201103有多数分，皮特在201102周<有多数分/ p>
获得的点的总数每播放机每周是容易的。但我不能工作，如何取最高从周群，并获得了结果，我所需要的。
我使用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查询与分组和排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
