VB.Net 数据表，根据最大值选择行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27982294/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VB.Net datatable, select rows based on maximum values
提问by Rajesh Thampi
I am trying to filter rows from a datatable based on the latest date value. Currently I am using dictionary object, which is not suitable for my actual objective, as my datatable will have more than two column, thus invalidating the key,value characteristics of a dictionary object. Bear with my limited knowledge, I am relative new to .net development and learning stuffs myself.
我正在尝试根据最新的日期值从数据表中过滤行。目前我正在使用字典对象，这不适合我的实际目标，因为我的数据表将有两列以上，从而使字典对象的键值特性无效。以我有限的知识，我对 .net 开发和学习内容相对较新。
My current coding is
我目前的编码是
Module Module1
Sub Main()
Dim names As Dictionary(Of String, DateTime) = New Dictionary(Of String, Date)
Dim dt As New DataTable
Dim strDetail As String = Nothing
dt.Columns.Add("Name")
dt.Columns.Add("punchdate", Type.GetType("System.DateTime"))
dt.Rows.Add("Rajesh", "01-jan-2014")
dt.Rows.Add("Rajesh", "01-feb-2014")
dt.Rows.Add("Rajesh", "01-apr-2014")
dt.Rows.Add("Rajesh", "01-sep-2014")
dt.Rows.Add("John", "15-sep-2014")
For Each row As DataRow In dt.Rows
strDetail = row.Item("Name")
'Debug.WriteLine(strDetail)
If names.ContainsKey(strDetail) Then
If names(strDetail) < row.Item("punchdate") Then
names(strDetail) = row.Item("punchdate")
End If
Else
names.Add(row.Item("Name"), row.Item("punchdate"))
End If
Next row
For Each kvp As KeyValuePair(Of String, DateTime) In names
Dim v1 As String = kvp.Key
Dim v2 As DateTime = kvp.Value
Debug.WriteLine(v1 + "; " + v2.ToString)
Next
End Sub
End Module
终端模块
Please let me know that should be optimal solution based on my requirement to return rows based on the max punchdate and more than just two columns.
请让我知道这应该是基于我的要求的最佳解决方案，即根据最大打孔日期和超过两列返回行。
regards,
问候，
回答by Dave Michener
Since you already have the values in a DataTableyou can use the Computemethod to find the most recent date.
由于您已经拥有 a 中的值，因此DataTable您可以使用Compute方法来查找最近的日期。
Dim maxDt as Object = dt.Compute("MAX(punchdate)", "")
Dim minDt as Object = dt.Compute("MIN(punchdate)", "")
In your example maxDtwould be the most recent entry for "John", "15-sep-2014", and minDtwould be the oldest entry for "Rajesh", "01-jan-2014".
在您的示例中maxDt，“John”的最新条目“15-sep-2014”minDt将是“Rajesh”的最旧条目“01-jan-2014”。
NOTE: I recommend casting maxDtand minDtto a Date data type:
注：我建议铸造maxDt和minDt一个日期数据类型：
Dim maxDt as Date = Date.Parse(dt.Compute("MAX(LAST_LOGIN_DATE)", "").ToString())
回答by Rajesh Thampi
I got it done, following a sample provided here
and I have modified my code like following & the final code block is as below
我修改了我的代码，如下所示，最终的代码块如下
Imports System.Linq
Module Module1 Dim dt As New DataTable
Module Module1 Dim dt As New DataTable
Sub Main()
' http://forums.asp.net/t/1826584.aspx?Select+DataRow+with+latest+date+using+LINQ
dt.Columns.Add("ID", Type.GetType("System.Int32"))
dt.Columns.Add("Name")
dt.Columns.Add("punchdate", Type.GetType("System.DateTime"))
dt.Rows.Add(100, "Rajesh", "01-jan-2014")
dt.Rows.Add(101, "Rajesh", "01-feb-2014")
dt.Rows.Add(102, "Rajesh", "01-apr-2014")
dt.Rows.Add(103, "Rajesh", "01-sep-2014")
dt.Rows.Add(104, "John", "15-sep-2014")
' Dim Max1 = rows.Max(Function(r) r.Field(Of DateTime)("punchdate"))
Dim myLINQ = From grp In From dt In dt.AsEnumerable() _
Group dt By GRP = dt.Field(Of String)("Name") Into Group _
Select New With { _
Key .ID = Group.Max(Function(T) T.Field(Of Int32)("ID")), _
Key .Name = GRP, _
Key .[Date] = Group.Max(Function(T) T.Field(Of DateTime)("punchdate")) _
}
Debug.WriteLine("=======================================")
For Each g In myLINQ
' Debug.WriteLine("Numbers that match '{0}':", g.Name)
Debug.WriteLine(g.Name & "----->" + g.Date.ToString)
Next
End Sub
End Module
终端模块
Thank you all!
谢谢你们！
回答by Kaustav Banerjee
Here is the code for you to select an entire row based on the maximum punchdate:
这是您根据最大打孔日期选择整行的代码：
Dim Max1 = rows.Max(Function(r) r.Field(DateTime)("punchdate"))
Dim rows=MyTable.Select("punchdate=Max1 ")
Alternatively if you want to select records from the datatable in a generic way, then you can use
或者，如果您想以通用方式从数据表中选择记录，则可以使用
TempDt =New DataView(Rows.CopyToDataTable()).ToTable(False,
"ID", "Name","punchdate")
回答by Kaustav Banerjee
For max punchdate you can use
对于最大打孔日期，您可以使用
Dim Max1 = rows.Max(Function(r) r.Field(Of <DateTime type>)("punchdate"))
and use
并使用
Dim rows=MyTable.Select("ID=Max1 ")
to return the entire row based on the selected max punch date.
根据选定的最大打卡日期返回整行。
if you want to separately select more than one column value for a row independently then you can use
如果要单独为一行单独选择多个列值，则可以使用
TempDt =New DataView(Rows.CopyToDataTable()).ToTable(False,
"columnname1", "name2","...","..")
