如何使用LINQ或其他方法将表数据分组并按多列求和[英] How to group table data and sum by multiple columns using LINQ or another approach

本文是小编为大家收集整理的关于如何使用LINQ或其他方法将表数据分组并按多列求和的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个包含类似于以下数据的表:

Shift--Name----------Department---Time
1      Employee1         360      100
1      Employee1         372      50
1      Employee1         385      300
2      Employee2         301      0
2      Employee2         301      0
2      Employee2         301      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         320      0
2      Employee2         333      0
2      Employee2         350      30
2      Employee2         350      35
2      Employee2         350      90
2      Employee2         350      0
2      Employee2         350      0
2      Employee2         350      90
2      Employee2         350      0
2      Employee2         350      0
2      Employee2         350      150
2      Employee2         350      50
2      Employee2         350      0
2      Employee2         350      60
2      Employee3         302      0
2      Employee3         305      0
2      Employee3         305      3
2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      10
2      Employee3         365      1
2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      10
2      Employee3         365      5
2      Employee4         314      0
2      Employee4         314      0
2      Employee4         314      100

我是 VB.NET 的新手,但我需要按班次、然后按名称、然后按部门输出上表分组,然后得到每个组的时间总和,如下所示:

Shift--Name----------Department---Time
1      Employee1         360      100
                         ***      100

1      Employee1         372      50
                         ***      50

1      Employee1         385      300
                         ***      300
       ***                        450
***                               450

2      Employee2         301      0
2      Employee2         301      0
2      Employee2         301      0
                         ***      0

2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
                         ***      0

2      Employee2         320      0
                         ***      0

2      Employee2         333      0
                         ***      0

2      Employee2         350      30
2      Employee2         350      35
2      Employee2         350      90
2      Employee2         350      0
2      Employee2         350      0
2      Employee2         350      90
2      Employee2         350      0
2      Employee2         350      0
2      Employee2         350      150
2      Employee2         350      50
2      Employee2         350      0
2      Employee2         350      60
                         ***      505
       ***                        505

2      Employee3         302      0
                         ***      0

2      Employee3         305      0
2      Employee3         305      3
                         ***      3

2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      10
2      Employee3         365      1
2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      10
2      Employee3         365      5
                         ***      26
       ***                        29

2      Employee4         314      0
2      Employee4         314      0
2      Employee4         314      100
                         ***      100
       ***                        100

所以对于employee1,我可以看到他们在360 部门总共花费了100 分钟,在372 部门花费了50 分钟,在385 部门花费了300 分钟.Employee1 的总时间是450 分钟.由于第一班只有一名员工,因此第一班的总分钟数为 450.关于如何制定 LINQ 查询以获得所需分组的任何建议?

如果有比使用 LINQ 更好的方法,我很乐意听到,我只是认为 LINQ 将是处理问题的最通用方法,我可以针对其他类似需求进行修改.

编辑:

我尝试了以下查询,但不完全理解它是如何获得我需要的结果的:

Dim TimeGroups =
    From j In TotalTimeResults
    Group By x = New With {
        Key .Shift = j.Field(Of String)("Shift"),
        Key .Name = j.Field(Of String)("Name"),
        Key .Time = j.Field(Of Integer)("Time")} Into g = Group
    Select New With {
        .Shift = x.Shift,
        .Name = x.Name,
        .Time = x.Time,
        .total = g.Sum(Function(r) r.Field(Of Integer)("Time"))
    }

我使用的表只是一个 VB.NET 数据表,其中显示了列和行.

推荐答案

我认为这会产生你所需要的:

Dim TimeGroups = From t In TotalTimeResults
                 Group t By t.Shift Into tsg = Group
                 Select New With { .Shift = Shift, .ShiftTotal = tsg.Sum(Function(t) t.Time),
                     .NameGroup = From t In tsg
                                 Group t By t.Name Into tng = Group
                                 Select New With {
                                     .Name = Name,
                                     .NameTotal = tng.Sum(Function(t) t.Time),
                                     .DeptGroup = From t In tng
                                                 Group t By t.Dept Into tdg = Group
                                                 Select New With { .Dept = Dept, .DeptTotal = tdg.Sum(Function(t) t.Time), .EmpInd = From t In tdg Select t.Time } } }

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

问题描述

I have a table containing data similar to the following:

Shift--Name----------Department---Time
1      Employee1         360      100
1      Employee1         372      50
1      Employee1         385      300
2      Employee2         301      0
2      Employee2         301      0
2      Employee2         301      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         320      0
2      Employee2         333      0
2      Employee2         350      30
2      Employee2         350      35
2      Employee2         350      90
2      Employee2         350      0
2      Employee2         350      0
2      Employee2         350      90
2      Employee2         350      0
2      Employee2         350      0
2      Employee2         350      150
2      Employee2         350      50
2      Employee2         350      0
2      Employee2         350      60
2      Employee3         302      0
2      Employee3         305      0
2      Employee3         305      3
2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      10
2      Employee3         365      1
2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      10
2      Employee3         365      5
2      Employee4         314      0
2      Employee4         314      0
2      Employee4         314      100

I'm new to VB.NET but I need to output the above table grouping by shift, then by name, then by department, and then get the sum of the time for each group, like the following:

Shift--Name----------Department---Time
1      Employee1         360      100
                         ***      100

1      Employee1         372      50
                         ***      50

1      Employee1         385      300
                         ***      300
       ***                        450
***                               450

2      Employee2         301      0
2      Employee2         301      0
2      Employee2         301      0
                         ***      0

2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
2      Employee2         305      0
                         ***      0

2      Employee2         320      0
                         ***      0

2      Employee2         333      0
                         ***      0

2      Employee2         350      30
2      Employee2         350      35
2      Employee2         350      90
2      Employee2         350      0
2      Employee2         350      0
2      Employee2         350      90
2      Employee2         350      0
2      Employee2         350      0
2      Employee2         350      150
2      Employee2         350      50
2      Employee2         350      0
2      Employee2         350      60
                         ***      505
       ***                        505

2      Employee3         302      0
                         ***      0

2      Employee3         305      0
2      Employee3         305      3
                         ***      3

2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      10
2      Employee3         365      1
2      Employee3         365      0
2      Employee3         365      0
2      Employee3         365      10
2      Employee3         365      5
                         ***      26
       ***                        29

2      Employee4         314      0
2      Employee4         314      0
2      Employee4         314      100
                         ***      100
       ***                        100

So for employee1, I can see that they spent a total of 100 minutes in department 360, 50 minutes in department 372, and 300 minutes in department 385. The total time for Employee1 is 450 minutes. Since there is only one employee on first shift, the total minutes for first shift is 450. Any suggestions on how I can formulate a LINQ query to get the desired grouping?

If there is a better way than using LINQ, I am open to hearing it, I just thought that LINQ would be the most versatile way to handle the problem that I can modify for other similar needs.

Edit:

I've tried the following query but don't fully understand how it works to get the results I need:

Dim TimeGroups =
    From j In TotalTimeResults
    Group By x = New With {
        Key .Shift = j.Field(Of String)("Shift"),
        Key .Name = j.Field(Of String)("Name"),
        Key .Time = j.Field(Of Integer)("Time")} Into g = Group
    Select New With {
        .Shift = x.Shift,
        .Name = x.Name,
        .Time = x.Time,
        .total = g.Sum(Function(r) r.Field(Of Integer)("Time"))
    }

The table I am using is just a VB.NET DataTable with the columns and rows as displayed.

推荐答案

I think this will result in what you need:

Dim TimeGroups = From t In TotalTimeResults
                 Group t By t.Shift Into tsg = Group
                 Select New With { .Shift = Shift, .ShiftTotal = tsg.Sum(Function(t) t.Time),
                     .NameGroup = From t In tsg
                                 Group t By t.Name Into tng = Group
                                 Select New With {
                                     .Name = Name,
                                     .NameTotal = tng.Sum(Function(t) t.Time),
                                     .DeptGroup = From t In tng
                                                 Group t By t.Dept Into tdg = Group
                                                 Select New With { .Dept = Dept, .DeptTotal = tdg.Sum(Function(t) t.Time), .EmpInd = From t In tdg Select t.Time } } }