多组数据[英] Multiple Group data

本文是小编为大家收集整理的关于多组数据的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我的 Payment 表中有如下一组数据

DateIssue             | Amount | CoursePaidForMonth | 
  2/3/2011 9:54:07 PM | 2000.00|          2
 2/27/2011 2:22:58 PM |   80.00|          2
 3/5/2011 11:14:56 PM |   80.00|          3
 3/27/2011 2:22:58 PM |   80.00|          2
 2/8/2011 6:32:45 PM  |   80.00|          2

我想对上面的数据进行两组分组:

  1. 截止日期是每个月的27号,所以我想把下个月27号到26号的所有数据分组.这是从 gName 成功完成的.没问题 !!如下图所示查看输出.

    DateIssue             AppendCommToMonthΞΞ CoursePaidForMonthΞΞ TotalAmountΞΞ 
    2/8/2011 6:32:45 PM            2                    2             2080.00
    2/27/2011 2:22:58 PM           3                    2              160.00
    3/27/2011 2:22:58 PM           4                    2               80.00
    
  2. 现在我想对同一个查询执行另一个分组.我也想对 CoursePaidForMonth 进行分组.意思是说,最终输出应该显示 4 行记录,而不是仅仅 3 行,它从本月 27 日到下个月 26 日分组,并按 CoursePaidForMonth 分组.如何做到这一点?

    DateIssue             AppendCommToMonthΞΞ CoursePaidForMonthΞΞ TotalAmountΞΞ 
    2/8/2011 6:32:45 PM            2                    2             2080.00
    2/27/2011 2:22:58 PM           3                    2              80.00
    3/5/2011 11:14:56 PM           3                    3              80.00
    3/27/2011 2:22:58 PM           4                    2              80.00
    

我的代码在这里:

var result = from p in db.Payments
               join soi in db.SaleOrderItems on p.ReferenceId equals soi.Id
               join ct in db.CourseByTutors on soi.InventoryOrCourseId equals ct.CourseId                              
               where p.PayType == (int)PayTypes.PayCourseFee
               && ct.TutorId == tutorId
               let gName = (p.DateIssue.Value.Day < 27) ? (p.DateIssue.Value.Month) : (p.DateIssue.Value.Month % 12 + 1)
               group p by new { gName} into g
               select new
               {
                  DateIssue = g.Select(x => x.DateIssue).First(),
                  AppendCommForWhichMonth = g.Key.gName,
                  CoursePaidForMonth = g.Select(x => x.CoursePaidForMonth).First(),
                                 TotalAmount = g.Sum(x => x.Amount),
               };

请指教..

推荐答案

您需要将 CoursePaidForMonth 包含到您的密钥中.像这样的:

var query = from p in db.Payments
            join soi in db.SaleOrderItems
                on p.ReferenceId equals soi.Id
            join cbt in db.CourseByTutors
                on soi.InventoryOrCourseId equals cbt.CourseId
            where p.PayType == (int)PayTypes.PayCourseFee && cbt.TutorId == tutorId
            orderby p.DateIssue
            let AppendCommForWhichMonth = p.DateIssue.Month + p.DateIssue.Day < 27 ? 0 : 1
            group p
                by new { AppendCommForWhichMonth, p.CoursePaidForMonth }
                into g
            select new
            {
                g.First().DateIssue,
                g.Key.AppendCommForWhichMonth,
                g.Key.CoursePaidForMonth,
                TotalAmount = g.Sum(p => p.Amount),
            };

本文地址:https://www.itbaoku.cn/post/1556788.html

问题描述

I have a set of data as below from my Payment table

DateIssue             | Amount | CoursePaidForMonth | 
  2/3/2011 9:54:07 PM | 2000.00|          2
 2/27/2011 2:22:58 PM |   80.00|          2
 3/5/2011 11:14:56 PM |   80.00|          3
 3/27/2011 2:22:58 PM |   80.00|          2
 2/8/2011 6:32:45 PM  |   80.00|          2

I would like to perform two sets of grouping for the data above:

  1. The closing date is on the 27 of every month, so I would like to group all the data from 27 till 26 of next month into a group. This is done successfully from the gName. NO PROBLEM !! Check out the output as the image below.

    DateIssue             AppendCommToMonthΞΞ CoursePaidForMonthΞΞ TotalAmountΞΞ 
    2/8/2011 6:32:45 PM            2                    2             2080.00
    2/27/2011 2:22:58 PM           3                    2              160.00
    3/27/2011 2:22:58 PM           4                    2               80.00
    
  2. Now I would like to perform another grouping on the same query. I would like to group the CoursePaidForMonth as well. Meaning to say, the final output should show 4 lines of records instead of just 3, it group by from 27 this month till 26 of next month into a group and also by CoursePaidForMonth. How to do this?

    DateIssue             AppendCommToMonthΞΞ CoursePaidForMonthΞΞ TotalAmountΞΞ 
    2/8/2011 6:32:45 PM            2                    2             2080.00
    2/27/2011 2:22:58 PM           3                    2              80.00
    3/5/2011 11:14:56 PM           3                    3              80.00
    3/27/2011 2:22:58 PM           4                    2              80.00
    

My code is here:

var result = from p in db.Payments
               join soi in db.SaleOrderItems on p.ReferenceId equals soi.Id
               join ct in db.CourseByTutors on soi.InventoryOrCourseId equals ct.CourseId                              
               where p.PayType == (int)PayTypes.PayCourseFee
               && ct.TutorId == tutorId
               let gName = (p.DateIssue.Value.Day < 27) ? (p.DateIssue.Value.Month) : (p.DateIssue.Value.Month % 12 + 1)
               group p by new { gName} into g
               select new
               {
                  DateIssue = g.Select(x => x.DateIssue).First(),
                  AppendCommForWhichMonth = g.Key.gName,
                  CoursePaidForMonth = g.Select(x => x.CoursePaidForMonth).First(),
                                 TotalAmount = g.Sum(x => x.Amount),
               };

Please advice..

推荐答案

You need to include the CoursePaidForMonth into your key. Something like this:

var query = from p in db.Payments
            join soi in db.SaleOrderItems
                on p.ReferenceId equals soi.Id
            join cbt in db.CourseByTutors
                on soi.InventoryOrCourseId equals cbt.CourseId
            where p.PayType == (int)PayTypes.PayCourseFee && cbt.TutorId == tutorId
            orderby p.DateIssue
            let AppendCommForWhichMonth = p.DateIssue.Month + p.DateIssue.Day < 27 ? 0 : 1
            group p
                by new { AppendCommForWhichMonth, p.CoursePaidForMonth }
                into g
            select new
            {
                g.First().DateIssue,
                g.Key.AppendCommForWhichMonth,
                g.Key.CoursePaidForMonth,
                TotalAmount = g.Sum(p => p.Amount),
            };