C# Linq 中的 Row_number 超过（按 xxx 分区）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9980568/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Row_number over (Partition by xxx) in Linq?
提问by Royi Namir
I have a DataTablewhich has this structure and data:
我有一个DataTable具有这种结构和数据的：
id | inst | name ------------------------ 1 | guitar | john 2 | guitar | george 3 | guitar | paul 4 | drums | ringo 5 | drums | pete
I can retrieve the records like this:
我可以像这样检索记录：
IEnumerable <Beatle>...
class Beatle
{
int id;
string inst;
string name;
}
I'd like to get the internal order of those who play the different instruments. In MSSQL I'd use
我想了解演奏不同乐器的人的内部顺序。在 MSSQL 中我会使用
SELECT
*
,Row_Number() OVER (PARTITION BY inst ORDER BY id) AS rn
FROM Beatles
This query returns
此查询返回
id | inst | name | rn ----------------------------- 1 | guitar | john | 1 2 | guitar | george | 2 3 | guitar | paul | 3 4 | drums | ringo | 1 5 | drums | pete | 2
How can I do that in Linq?
我怎样才能在Linq 中做到这一点？
Edit.(after accepted answer)
编辑。（接受答案后）
Full working code :
完整的工作代码：
var beatles = (new[] { new { id=1 , inst = "guitar" , name="john" },
new { id=2 , inst = "guitar" , name="george" },
new { id=3 , inst = "guitar" , name="paul" },
new { id=4 , inst = "drums" , name="ringo" },
new { id=5 , inst = "drums" , name="pete" }
});
var o = beatles.OrderBy(x => x.id).GroupBy(x => x.inst)
.Select(g => new { g, count = g.Count() })
.SelectMany(t => t.g.Select(b => b)
.Zip(Enumerable.Range(1, t.count), (j, i) => new { j.inst, j.name, rn = i }));
foreach (var i in o)
{
Console.WriteLine("{0} {1} {2}", i.inst, i.name, i.rn);
}
采纳答案by Rabbi
B"H
B"H
I know this is old. But why isn't the solution simply?
我知道这是旧的。但为什么解决方案不简单呢？
var o = beatles.GroupBy(x => x.inst)
.SelectMany(g =>
g.Select((j, i) => new { j.inst, j.name, rn = i + 1 })
);
回答by Ethan Brown
As @The_Smallest points out, row number is not supported by LINQ. Here's how you can get what you're looking for, though:
正如@The_Smallest 指出的，LINQ 不支持行号。不过，您可以通过以下方式获得所需内容：
var grouped = beatles.OrderBy( x => x.id )
.ToList() // required because SelectMany below doesn't evaluate to SQL
.GroupBy( x => x.inst );
var rns = grouped.ToDictionary( x => x.Key, x => 1 );
var result = grouped
.SelectMany( x => x.Select(
y => new { inst = y.inst, name = y.name, rn = rns[y.inst]++ } ) );
回答by leppie
Another idea is using a view, if possible.
如果可能，另一个想法是使用视图。
回答by Jon Comtois
Try this one liner:
试试这个衬垫：
var o = beatles
.OrderBy( x => x.id )
.GroupBy( x => x.inst )
.Select( group => new { Group = group, Count = group.Count() } )
.SelectMany( groupWithCount =>
groupWithCount.Group.Select( b => b)
.Zip(
Enumerable.Range( 1, groupWithCount.Count ),
( j, i ) => new { j.inst, j.name, RowNumber = i }
)
);
foreach (var i in o)
{
Console.WriteLine( "{0} {1} {2}", i.inst, i.name, i.RowNumber );
}
Output:
输出：
Guitar John 1
Guitar George 2
Guitar Paul 3
drums Ringo 1
drums Pete 2
回答by Tomasz Maj
Some may find useful using in your code to get proper index
有些人可能会发现在您的代码中使用以获得正确的索引很有用
.Select((item, i) => new { Item = item, Index = i })
回答by Benjamin Fourgeaud
Another solution to do the equivalent of RANK() OVER (PARTITION BY "partitionBy" ORDER BY "orderBy" DESC):
另一种等效于 RANK() OVER (PARTITION BY "partitionBy" ORDER BY "orderBy" DESC) 的解决方案：
DataTable Rank(DataTable dt, string partitionBy, string orderBy, int whichRank)
{
DataView dv = new DataView(dt);
dv.Sort = partitionBy + ", " + orderBy + " DESC";
DataTable rankDt = dv.ToTable();
rankDt.Columns.Add("Rank");
int rank = 1;
for (int i = 0; i < rankDt.Rows.Count - 1; i++)
{
rankDt.Rows[i]["Rank"] = rank;
DataRow thisRow = rankDt.Rows[i];
DataRow nextRow = rankDt.Rows[i + 1];
if (thisRow[partitionBy].ToString() != nextRow[partitionBy].ToString())
rank = 1;
else
rank++;
}
DataView selectRankdv = new DataView(rankDt);
selectRankdv.RowFilter = "rank = " + whichRank;
return selectRankdv.ToTable();
}
回答by shA.t
Another solution for Linq to objects is:
Linq to objects 的另一个解决方案是：
var result = beatles
.GroupBy(g => g.inst)
// PARTITION BY ^^^^
.Select(c => c.OrderBy(o => o.id).Select((v, i) => new { i, v }).ToList())
// ORDER BY ^^
.SelectMany(c => c)
.Select(c => new { c.v.id, c.v.inst, c.v.name, rn = c.i + 1 })
.ToList();
