分组和数据表总和由不同的两个领域具备条件 [英] Grouping and Sum Datatable by two fields with different Where conditions

问题描述

我有样本数据的DataTable像下面。

I have a datatable with sample data like below.

UserName    IsBillable    Hours
-----------------------------
user1       Yes           10
user2       Yes           15
user3       No            20
user1       Yes           25
user1       No            15
user2       Yes           10

和我所想组 IsBillable 和金额与小时列。

And I want to group by IsBillable and sum with Hours column.

UserName    Billable    NonBillable
-----------------------------
user1       35           15
user2       25           0
user3       0            20

我怎么可以这样用<？code> LINQ

推荐答案

您可以尝试这样的事：

var grouped = dt.AsEnumerable()
                .GroupBy(row=> row.Field<string>("UserName"))
                .Select(gr=> new 
                {
                    UserName = gr.Key,
                    Billable = gr.Where(row=>row.Field<string>("IsBillable")=="Yes")
                                 .Sum(row=>row.Field<int>("Hours"),
                    NonBillable = gr.Where(row=>row.Field<string>("IsBillable")=="No")
                                    .Sum(row=>row.Field<int>("Hours"),
                });

我假设 DT 是数据表你只需要一个序列的项目具有三个属性用户名，结算， NonBillable 。如果您需要的结果是一个DataTable，你需要一些额外的工作。请让我知道，如果是这样的话。

I have assumed that dt is your DataTable and you just need a sequence of items with three properties UserName, Billable, NonBillable. If you need your result to be a DataTable, you need some extra work. Please let me know, if that's the case.

我们在上面做什么，它的简单。 Intially我们组我们的研究结果基于用户的姓名，然后我们筛选各组项目，以计算出相应的款项。

What we are doing above, it's straightforward. Intially we group our results based on the user's name and then we filter the items of each group, in order to calculate the corresponding sums.

这篇关于分组和数据表总和由不同的两个领域具备条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！