代码之家 › 专栏 › 技术社区 › DooDoo

在最新组上应用分组依据

linq-to-entities group-by linq c#

DooDoo · 技术社区 · 7 年前

请考虑下这张表:

Organization           State          Year          Month        Value
----------------------------------------------------------------------
   O1                   NY             2017          1            1
   01                   WA             2017          1            2
   01                   SA             2017          1            3
   O1                   NY             2017          2            4
   01                   WA             2017          2            5
   01                   SA             2017          2            6
   O2                   NY             2015          9            7
   02                   WA             2015          9            8
   02                   SA             2015          9            9
   O2                   NY             2016          1            10
   02                   WA             2016          1            11
   02                   SA             2016          1            12
   O3                   NY             2017          8            13
   03                   WA             2017          8            14
   03                   SA             2017          8            15

我要创建以下结果:

Organization           Year          Month        Sum
------------------------------------------------------
    01                 2017            2           15
    02                 2016            1           33
    03                 2017            8           42

我想分组讨论最新情况 Year, Month 计算总和。在上面的示例中,组织01有两个时段的数据,但我想按最新时段分组。

var query = from o in MyList
            group o by new {c.Organization, c.Year , c.Month} int grp
            select new 
            { 
                grp.Key.Organization, 
                grp.Key.Year, 
                grp.Key.Month, 
                grp.Sum() 
            };

2 回复 | 直到 7 年前

er-sho 7 年前

尝试以下查询:

class Program
{
    static void Main(string[] args)
    {           

        var results = (from o in MyList
                       group o by new { o.Organization } into g
                       select new
                       {
                           Org_Id = g.Key.Organization,
                           Year = g.Select(x => x.Year)
                                   .Max(),
                           Month = g.Where(x => x.Year == g.Select(y => y.Year).Max())
                                    .Select(z => z.Month)
                                    .Max(),
                           Sum = g.Where(x => x.Month == g.Where(y => y.Year == g.Select(z => z.Year).Max())
                                                          .Select(y => y.Month)
                                                          .Max())
                                  .Select(z => z.Value)
                                  .Sum()
                       }).ToList();

        results.ForEach(x => Console.WriteLine($"Org_Id: {x.Org_Id} \t Year: {x.Year} \t Month: {x.Month} \t Sum: {x.Sum}"));

        Console.ReadLine();
    }
}

我们在查询中所做的:

1) 分组依据 Organization

2) Org_Id

3) Year Max 来自组的年份。

Month :选择通过选择 马克斯

5) Sum :选择值的总和年月日 马克斯

输出:

上述查询的Sql:

SELECT [t1].[Organization] AS [Org_Id], (
    SELECT MAX([t2].[Year])
    FROM [Org] AS [t2]
    WHERE (([t1].[Organization] IS NULL) AND ([t2].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t2].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t2].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t2].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t2].[Organization]))))
    ) AS [Year], (
    SELECT MAX([t3].[Month])
    FROM [Org] AS [t3]
    WHERE ([t3].[Year] = ((
        SELECT MAX([t4].[Year])
        FROM [Org] AS [t4]
        WHERE (([t1].[Organization] IS NULL) AND ([t4].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t4].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t4].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t4].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t4].[Organization]))))
        ))) AND ((([t1].[Organization] IS NULL) AND ([t3].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t3].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t3].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t3].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t3].[Organization])))))
    ) AS [Month], (
    SELECT SUM([t5].[Value])
    FROM [Org] AS [t5]
    WHERE ([t5].[Month] = ((
        SELECT MAX([t6].[Month])
        FROM [Org] AS [t6]
        WHERE ([t6].[Year] = ((
            SELECT MAX([t7].[Year])
            FROM [Org] AS [t7]
            WHERE (([t1].[Organization] IS NULL) AND ([t7].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t7].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t7].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t7].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t7].[Organization]))))
            ))) AND ((([t1].[Organization] IS NULL) AND ([t6].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t6].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t6].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t6].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t6].[Organization])))))
        ))) AND ((([t1].[Organization] IS NULL) AND ([t5].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t5].[Organization] IS NOT NULL) AND ((([t1].[Organization] IS NULL) AND ([t5].[Organization] IS NULL)) OR (([t1].[Organization] IS NOT NULL) AND ([t5].[Organization] IS NOT NULL) AND ([t1].[Organization] = [t5].[Organization])))))
    ) AS [Sum]
FROM (
    SELECT [t0].[Organization]
    FROM [Org] AS [t0]
    GROUP BY [t0].[Organization]
    ) AS [t1]

输出:

ManishM 7 年前

我认为你得到输出的逻辑是错误的。我从输出分组中看到的是分两步完成的

第1步

Group By Organization, State, MAX(Year) AS Year, MAX(Month) AS Month, MAX(Value) As Value

第二步

Group By Organization, MAX(Year) AS Year, MAX(Month) AS Month, SUM(Value) As Value

组织在每个状态下的最大值之和

如果我的假设是正确的,那么您将能够为此编写LINQ查询