本文是小编为大家收集整理的关于如何使用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 } } }
问题描述
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 } } }
更多相关问答
联系站长