如何用C# LINQ实现SQL Server多列分区的Row_Number()窗口函数

表数据 #

目标SQL查询 #

SELECT  
    LocationID,
    AFEID,
    ContractorID,
    ResourceID,
    MAX(ST) AS MaxST,
    MAX(OT) AS MaxOT,
    MAX(DT) AS MaxDT,
    AVG(ST) AS AvgST,
    AVG(OT) AS AvgOT,
    AVG(DT) AS AvgDT,
    MIN(ST) AS MinST,
    MIN(OT) AS MinOT,
    MIN(DT) AS MinDT,
    CostDate,
    ROW_NUMBER() OVER (PARTITION BY afeid, contractorid, resourceid 
                       ORDER BY costdate DESC) AS rownum              
FROM 
    tbldata 
GROUP BY 
    LocationID, AFEID, ContractorID,
    ResourceID, CostDate

尝试过的LINQ查询（未成功） #

tbldata.OrderByDescending(x => x.CostDate)
       .AsEnumerable()
       .GroupBy(x => new
       {
           x.LocationId,
           x.AfeId,
           x.ContractorId,
           x.ResourceId,
           x.CostDate,
       })
        .Select(grp => new
        {
            grp.Key.AfeId,
            grp.Key.ContractorId,
            grp.Key.ResourceId,
            grp.Key.LocationId,
            grp.Key.CostDate,
            MaxST = grp.Max(x => x.StandardTime),
            MaxOT = grp.Max(x => x.Overtime),
            MaxDT = grp.Max(x => x.DualTime),
            AvgST = grp.Average(x => x.StandardTime),
            AvgOT = grp.Average(x => x.Overtime),
            AvgDT = grp.Average(x => x.DualTime),
            MinST = grp.Min(x => x.StandardTime),
            MinOT = grp.Min(x => x.Overtime),
            MinDT = grp.Min(x => x.DualTime),
            count = grp.Count(),
            rownum = grp.Zip(Enumerable.Range(1, grp.Count()), (j, i) => new { rownum = i}).FirstOrDefault()
        });

期望输出 #

解决方案 #

你原来的LINQ逻辑顺序错误：SQL中是先分组得到每日聚合数据，再对这些聚合数据按AFEID、ContractorID、ResourceID分区，按CostDate降序生成行号。而你之前的代码是先排序，再分组，然后试图在每个日期分组内生成行号，这和SQL逻辑不符。

正确的LINQ实现需要分两步：

1. 先按LocationID、AFEID、ContractorID、ResourceID、CostDate分组，计算聚合值，得到每个日期分组的统计结果。
2. 对聚合结果按AFEID、ContractorID、ResourceID分区，在每个分区内按CostDate降序排序，然后生成行号。

完整的LINQ代码如下：

var result = tbldata
    // 第一步：对应SQL的GROUP BY，生成每日聚合数据
    .GroupBy(x => new
    {
        x.LocationId,
        x.AfeId,
        x.ContractorId,
        x.ResourceId,
        x.CostDate
    })
    .Select(grp => new
    {
        grp.Key.LocationId,
        grp.Key.AfeId,
        grp.Key.ContractorId,
        grp.Key.ResourceId,
        grp.Key.CostDate,
        MaxST = grp.Max(x => x.StandardTime),
        MaxOT = grp.Max(x => x.Overtime),
        MaxDT = grp.Max(x => x.DualTime),
        AvgST = grp.Average(x => x.StandardTime),
        AvgOT = grp.Average(x => x.Overtime),
        AvgDT = grp.Average(x => x.DualTime),
        MinST = grp.Min(x => x.StandardTime),
        MinOT = grp.Min(x => x.Overtime),
        MinDT = grp.Min(x => x.DualTime)
    })
    // 第二步：对应SQL的PARTITION BY，按AFEID、ContractorID、ResourceID分区
    .GroupBy(x => new { x.AfeId, x.ContractorId, x.ResourceId })
    // 在每个分区内按CostDate降序排序，生成行号
    .SelectMany(partition => 
        partition.OrderByDescending(item => item.CostDate)
                 .Select((item, index) => new
                 {
                     rownum = index + 1, // 索引从0开始，所以+1得到行号
                     item.LocationId,
                     item.AfeId,
                     item.ContractorId,
                     item.ResourceId,
                     item.MaxST,
                     item.MaxOT,
                     item.MaxDT,
                     item.AvgST,
                     item.AvgOT,
                     item.AvgDT,
                     item.MinST,
                     item.MinOT,
                     item.MinDT,
                     item.CostDate
                 })
    )
    .ToList();

这段代码完全对应你的SQL逻辑，执行后就能得到你期望的输出结果。

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