在C#LINQ中实现RANK OVER SQL子句 [英] Implementing RANK OVER SQL Clause in C# LINQ
本文介绍了在C#LINQ中实现RANK OVER SQL子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要实现以下T-SQL子句....
I need to implement the following T-SQL clause ....
RANK() OVER (PARTITION BY a.CategoryKey ORDER BY (x.Rate * @BASE_RATE ) DESC )as Rank
...在C#LINQ中.到目前为止,我想出的是类似....
...in C# LINQ. So far what I've come up with is something like ....
var rank = data.GroupBy(d => d.CategoryKey)
.Select(group => group.OrderByDescending(g => g.Rate * @BAES_RATE)
我认为,这将给我每个按速率* BASE_RATE排序的等级分区.但是我真正需要的是单行的个人排名,这是更大结果中的子查询.所以实际上我正在使用的完整SQL查询就像....
I think this would give me each rank partition ordered by rate * BASE_RATE. But what I actually need is the individual rank of a single row, with this being a subquery within a larger result. So really the full SQL query I'm working from is something like ....
SELECT
a.Rate,
a.CategoryKey,
a.ID,
.
.
.
RANK() OVER (PARTITION BY a.CategoryKey ORDER BY (x.Rate * @BASE_RATE ) DESC )as Rank
FROM data
推荐答案
如果您不需要确切的排名语义(即并列排名). 您可以使用精选投影可用的索引
If you don't need the exact Rank semantics (i.e. tied ranks). You can use the index available with select projections
var rank = data.GroupBy(d => d.CategoryKey)
.SelectMany(g => g.OrderByDescending(y => y.Rate * @BAES_RATE)
.Select((x,i) => new{g.Key, Item=x, Rank=i+1}))
否则,您可以查看此答案
这篇关于在C#LINQ中实现RANK OVER SQL子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文