在两个数据表中查找缺失值 [英] Finding missing values in two datatable

问题描述

嗨


我有两个数据表 CurrentPayrollTable 和 PreviousPayrollTable 。我需要比较这两个表，找出退出 CurrentPayrollTable 表的员工编号，但不存在于 PreviousPayrollTable 中员工编号，存在于 PreviousPayrollTable 中，但不在 CurrentPayrollTable 中。我尝试使用以下查询但收到错误。





本期杂志中的任何帮助/指针都将受到高度赞赏。



谢谢

Gaurav



我的尝试：

Hi
I have two data tables CurrentPayrollTable and PreviousPayrollTable. I need to compare these two tables to find out the employee number which exits in CurrentPayrollTable table but not exist in PreviousPayrollTable and employee number which exists in PreviousPayrollTable but not in CurrentPayrollTable. I tried to use below query but getting error.


Any help/pointer in this issue will be highly appreciated.

Thanks
Gaurav

What I have tried:

(From rowSheet1 In CurrentPayrollTable.AsEnumerable()
                    Where    (From rowSheet2 In PreviousPayrollTable.AsEnumerable()
                              Where !CInt(rowSheet1("Employee Number").ToString).Contains(CInt(rowSheet2("Employee Number").ToString))
                              Select rowSheet2).Count>0
Select rowSheet1).UNION(
(From rowSheet2 In PreviousPayrollTable.AsEnumerable()
Where    (From rowSheet1 In CurrentPayrollTable.AsEnumerable()
Where  	!CInt(rowSheet1("Employee Number").ToString).Contains(CInt(rowSheet2("Employee Number").ToString))                             						   
                               Select rowSheet1).Count>0
                    Select rowSheet2)).CopyToDatatable()

推荐答案

尝试这样的事情：

Try something like this:

Dim currentEmployees As IEnumerable(Of Integer) = CurrentPayrollTable.AsEnumerable().Select(Function (row) row.Field(Of Integer)("Employee Number"))
Dim previousEmployees As IEnumerable(Of Integer) = PreviousPayrollTable.AsEnumerable().Select(Function (row) row.Field(Of Integer)("Employee Number"))

Dim currentEmployeesNotInPrevious As IEnumerable(Of Integer) = currentEmployees.Except(previousEmployees)
Dim previousEmployeesNotInCurrent As IEnumerable(Of Integer) = previousEmployees.Except(currentEmployees)

' To get the employees who are not in both tables:
Dim employeesMissing As New HashSet(Of Integer)(currentEmployees)
employeesMissing.SymmetricExceptWith(previousEmployees)
' employeesMissing now contains employees who are in one table but not the other.

这篇关于在两个数据表中查找缺失值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！