在数据表VB.NET中获取所需值的总和（使用过滤器） [英] Get sum of required values (with filter) in datatable VB.NET

问题描述

I have a datatable with the following fields:

Day
Date
Room Rate
No of Person
Amount
The data is as follows:

<pre>
Day Date    Room No.    Room Rate   No. of Person       Amount
1   4/9/2018    101         900.00      2           1, 800.00
2   4/10/2018   101         900.00      2           1, 800.00
3   4/10/2018   101         900.00      2           1, 800.00
1   4/9/2018    102         1000.00     3           3, 000.00
2   4/10/2018   102         1000.00     3           3, 000.00
3   4/10/2018   102         1000.00     3           3, 000.00

我想通过获得金额总和得到总金额。但是，不应包括每个房间的最后一天。通过上面的例子，总金额将是9,600.00，因为不包括第101天的房间101和房间102.



我试图使用数据表计算功能，但这不会有效：

I would like to get the total amount by getting the sum of Amount. But, the last day for each Room should not be included. With the above example, the total amount would be 9, 600.00 since Room 101 and Room 102 of day 3 is not included.

I tried to use the datatable compute function, but this will not be effective:

Convert.ToInt32(DataSet.Tables("dt_Lodging").Compute("SUM(Amount)", "Day = 3") 

日不限于3.如果我们有第1天到第5天，第5天是一个不包括在Total中的那个。



有人建议使用公式计算房间价格*晚上的天数（而不是天数。但我有从第一天到入住指定房间的最后一天显示房间的记录。



我尝试过： < br $>

Day will not be limited to 3. If we have days 1 to 5, day 5 is the one which will not be included in Total.

Some suggested to compute it using the formula Room Rate * Number of nights (instead of number of days. But I have to display record for rooms from the very first day til the last day of staying in a specified room.

What I have tried:

Convert.ToInt32(DataSet.Tables("dt_Lodging").Compute("SUM(Amount)", "Day = 3") 

推荐答案

检查：

Check this:

Dim dt As DataTable = New DataTable()
dt.Columns.AddRange({New DataColumn("Day", GetType(Int32)), _
				New DataColumn("Date", GetType(DateTime)), _
				New DataColumn("Room No.", GetType(Int32)), _
				New DataColumn("Room Rate", GetType(Double)), _
				New DataColumn("No. of Person", GetType(Int32)), _
				New DataColumn("Amount", GetType(Double))})
dt.Rows.Add(New Object(){1, New DateTime(2018, 4, 9), 101, 900.00, 2, 1800.00})
dt.Rows.Add(New Object(){2, New DateTime(2018, 4, 10), 101, 900.00, 2, 1800.00})
dt.Rows.Add(New Object(){3, New DateTime(2018, 4, 10), 101, 900.00, 2, 1800.00})
dt.Rows.Add(New Object(){1, New DateTime(2018, 4, 9), 102, 1000.00, 3, 3000.00})
dt.Rows.Add(New Object(){2, New DateTime(2018, 4, 10), 102, 1000.00, 3, 3000.00})
dt.Rows.Add(New Object(){3, New DateTime(2018, 4, 10), 102, 1000.00, 3, 3000.00})


Dim result = dt.AsEnumerable() _
	.GroupBy(Function(x) x.Field(Of Int32)("Room No.")) _
	.Select(Function(grp) New With _
	{ _
		.RoomNo = grp.Key, _
		.TotalAmt = grp.Take(grp.Count-1).Sum(Function(x) x.Field(Of Double)("Amount")) _
	}) _
	.Sum(Function(x) x.TotalAmt) '9600 ;)

Linq [ ^ ]让生活更轻松:)请参阅： Visual Basic中的LINQ简介| Microsoft Docs [ ^ ]

DataTableExtensions.AsEnumerable Method（DataTable）（System.Data） [ ^ ]



几句解释：

Linq[^] makes life easier :) See: Introduction to LINQ in Visual Basic | Microsoft Docs[^]
DataTableExtensions.AsEnumerable Method (DataTable) (System.Data)[^]

Few words of explanation:

Dim result = dt.AsEnumerable() _ 'Returns an IEnumerable<T> object, where the generic parameter T is DataRow
	.GroupBy(Function(x) x.Field(Of Int32)("Room No.")) _ 'group data by [Room No.]
	.Select(Function(grp) New With _ 'select
	{ _
		.RoomNo = grp.Key, _ '[Room No.]
		.TotalAmt = grp.Take(grp.Count-1) 'skip last row!!!
                          .Sum(Function(x) x.Field(Of Double)("Amount")) _ 'get sum of amount for each room
	}) _
	.Sum(Function(x) x.TotalAmt) 'get total amount

Enumerable.GroupBy（TSource，TKEY的，TElement）方法（IEnumerable的（TSource），函数功能（TSource，TKEY的），函数功能（TSource，TElement ））（System.Linq） [ ^ ]

Enumerable.Count（TSource）方法（IEnumerable（TSource），Func（TSource，Boolean））（System.Linq） [ ^ ]

Enumerable.Take（TSource）方法（IEnumerable（TSource），Int32）（System.Linq） [ ^ ]

Enumerable.Sum（TSource）方法（IEnumerable（TSource），Func （TSource，Double））（System.Linq） [ ^ ]

Enumerable.GroupBy(TSource, TKey, TElement) Method (IEnumerable(TSource), Func(TSource, TKey), Func(TSource, TElement)) (System.Linq)[^]
Enumerable.Count(TSource) Method (IEnumerable(TSource), Func(TSource, Boolean)) (System.Linq)[^]
Enumerable.Take(TSource) Method (IEnumerable(TSource), Int32) (System.Linq)[^]
Enumerable.Sum(TSource) Method (IEnumerable(TSource), Func(TSource, Double)) (System.Linq)[^]

这篇关于在数据表VB.NET中获取所需值的总和（使用过滤器）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！