多个组数据 [英] Multiple Group data
问题描述
我有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
我想对上述数据进行两组分组:
I would like to perform two sets of grouping for the data above:
-
截止日期为每月27日,因此我想将下个月27日至26日的所有数据分组.这是从
gName
成功完成的.没问题 !!如下图所示,检查输出.
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
现在,我想对同一查询执行另一个分组.我也想对CoursePaidForMonth
进行分组.意思是说,最终输出应显示4行记录而不是3条记录,它将本月的27个到下个月的26个分组,并按CoursePaidForMonth
分组.该怎么做?
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
我的代码在这里:
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
.像这样:
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),
};
这篇关于多个组数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!