PowerShell比較兩個資料表-兩列上的過濾器問題

問題：當我嘗試將兩個資料表與兩列進行比較時（顯示列 A=A 列，B 列不存在的位置），我似乎找不到匹配項。

[下面的示例資料]

如何匹配 [僅兩個表共有的組] 并顯示 [匹配組中缺少誰]？當我嘗試時，我只能顯示任何組中缺少的人（又名查理）。我還需要 Bob 出現在 B 組。

要重現此問題 - 在 PowerShell 中運行此代碼段：

## Live data table
# create "live" data:
$dtLive = New-Object System.Data.DataTable
$dtLive.Columns.Add("GroupEmailAddress") 
$dtLive.Columns.Add("MemberEmailAddress")

# add Alex to both groups, add Bob to just A-Group
$row = $dtLive.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtLive.Rows.Add($Row)

$row = $dtLive.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtLive.Rows.Add($Row)

$row = $dtLive.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtLive.Rows.Add($Row)

## Updated data table
# mimic existing "live" data:
$dtUpdated = New-Object System.Data.DataTable
$dtUpdated.Columns.Add("GroupEmailAddress") 
$dtUpdated.Columns.Add("MemberEmailAddress")

# add Alex to both groups, add Bob to just A-Group
$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

## NOW THE ACTUAL UPDATES
# add Bob to A-Group
$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

# add new person Charlie to A-Group
$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

# add new person Dan to (new) C-Group
$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

此代碼段顯示了共享相同組名的所有條目：

$dtUpdated | Where-Object {($_.GroupEmailAddress -in $dtLive.GroupEmailAddress)}

結果：

GroupEmailAddress   MemberEmailAddress
-----------------   ------------------
A-Group@company.com alex@company.com
B-Group@company.com alex@company.com
A-Group@company.com bob@company.com
B-Group@company.com bob@company.com
A-Group@company.com charlie@company.com

此片段僅顯示 LIVE 資料表中缺少的成員電子郵件地址（無論組成員身份如何）：

$dtUpdated | Where-Object {($_.MemberEmailAddress -notin $dtLive.MemberEmailAddress)}

結果：

GroupEmailAddress   MemberEmailAddress
-----------------   ------------------
A-Group@company.com charlie@company.com
C-Group@company.com dan@company.com

因為我想匹配缺少的組，所以我們不想要 Dan（C 組不在實時表中）。我們想要查理（A 組）和鮑勃（B 組）

如果我運行這樣的東西：

$dtUpdated | Where-Object {($_.GroupEmailAddress -in $dtLive.GroupEmailAddress) -and ($_.MemberEmailAddress -notin $dtLive.MemberEmailAddress)}

或者

$dtUpdated | Where-Object {($_.GroupEmailAddress -in $dtLive.GroupEmailAddress)} | Where-Object {($_.MemberEmailAddress -notin $dtLive.MemberEmailAddress)}

我最終只過濾到查理（任何組的新手）。如何將 B 組中的 Bob 丟失？

謝謝！！

uj5u.com熱心網友回復：

如果我理解正確，這應該可以作業，基本上使用示例表添加的新行將$dtLive是：

[email protected] -> [email protected]
[email protected] -> [email protected]
[email protected] -> [email protected]

代碼將是：

$map = $dtLive.Rows | Group-Object GroupEmailAddress -AsHashTable -AsString
foreach($row in $dtUpdated.Rows) {
    # if this `GroupEmailAddress` doesn't exist in `$dtLive`
    if($row.GroupEmailAddress -notin $dtLive.GroupEmailAddress) {
        $dtLive.ImportRow($row)
        continue
    }
    # if this `MemberEmailAddress` doesn't exist in the Group Of `GroupEmailAddress`
    if($row.MemberEmailAddress -notin $map[$row.GroupEmailAddress].MemberEmailAddress) {
        $dtLive.ImportRow($row)
    }
}

檢查時的最終輸出$dtLive：

GroupEmailAddress   MemberEmailAddress
-----------------   ------------------
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]

標籤：电源外壳数据表

上一篇：驗證和比較powershell腳本中的傳入引數

下一篇：無法將值轉換為型別“System.Int32”