这个
Join-Object
(别名)
Join
)函数将两个对象数组中的列组合成一个新的对象数组,该数组可以另存为表。(
Export-CSV
)或按原样使用。
Function Join-Object { # https://powersnippets.com/join-object/
[CmdletBinding()]Param ( # Version 02.02.00, by iRon
[Object[]]$RightTable, [Alias("Using")]$On, $Merge = @{}, [Parameter(ValueFromPipeLine = $True)][Object[]]$LeftTable, [String]$Equals
)
$Type = ($MyInvocation.InvocationName -Split "-")[0]
$PipeLine = $Input | ForEach {$_}; If ($PipeLine) {$LeftTable = $PipeLine}
If ($LeftTable -eq $Null) {If ($RightTable[0] -is [Array]) {$LeftTable = $RightTable[0]; $RightTable = $RightTable[-1]} Else {$LeftTable = $RightTable}}
$DefaultMerge = If ($Merge -is [ScriptBlock]) {$Merge; $Merge = @{}} ElseIf ($Merge."") {$Merge.""} Else {{$Left.$_, $Right.$_}}
If ($Equals) {$Merge.$Equals = {If ($Left.$Equals -ne $Null) {$Left.$Equals} Else {$Right.$Equals}}}
ElseIf ($On -is [String] -or $On -is [Array]) {@($On) | ForEach {If (!$Merge.$_) {$Merge.$_ = {$Left.$_}}}}
$LeftKeys = @($LeftTable[0].PSObject.Properties | ForEach {$_.Name})
$RightKeys = @($RightTable[0].PSObject.Properties | ForEach {$_.Name})
$Keys = $LeftKeys + $RightKeys | Select -Unique
$Keys | Where {!$Merge.$_} | ForEach {$Merge.$_ = $DefaultMerge}
$Properties = @{}; $LeftOut = @($True) * @($LeftTable).Length; $RightOut = @($True) * @($RightTable).Length
For ($LeftIndex = 0; $LeftIndex -lt $LeftOut.Length; $LeftIndex++) {$Left = $LeftTable[$LeftIndex]
For ($RightIndex = 0; $RightIndex -lt $RightOut.Length; $RightIndex++) {$Right = $RightTable[$RightIndex]
$Select = If ($On -is [String]) {If ($Equals) {$Left.$On -eq $Right.$Equals} Else {$Left.$On -eq $Right.$On}}
ElseIf ($On -is [Array]) {($On | Where {!($Left.$_ -eq $Right.$_)}) -eq $Null} ElseIf ($On -is [ScriptBlock]) {&$On} Else {$True}
If ($Select) {$Keys | ForEach {$Properties.$_ =
If ($LeftKeys -NotContains $_) {$Right.$_} ElseIf ($RightKeys -NotContains $_) {$Left.$_} Else {&$Merge.$_}
}; New-Object PSObject -Property $Properties; $LeftOut[$LeftIndex], $RightOut[$RightIndex] = $Null
} } }
If ("LeftJoin", "FullJoin" -Contains $Type) {
For ($LeftIndex = 0; $LeftIndex -lt $LeftOut.Length; $LeftIndex++) {
If ($LeftOut[$LeftIndex]) {$Keys | ForEach {$Properties.$_ = $LeftTable[$LeftIndex].$_}; New-Object PSObject -Property $Properties}
} }
If ("RightJoin", "FullJoin" -Contains $Type) {
For ($RightIndex = 0; $RightIndex -lt $RightOut.Length; $RightIndex++) {
If ($RightOut[$RightIndex]) {$Keys | ForEach {$Properties.$_ = $RightTable[$RightIndex].$_}; New-Object PSObject -Property $Properties}
} }
}; Set-Alias Join Join-Object
Set-Alias InnerJoin Join-Object; Set-Alias InnerJoin-Object Join-Object -Description "Returns records that have matching values in both tables"
Set-Alias LeftJoin Join-Object; Set-Alias LeftJoin-Object Join-Object -Description "Returns all records from the left table and the matched records from the right table"
Set-Alias RightJoin Join-Object; Set-Alias RightJoin-Object Join-Object -Description "Returns all records from the right table and the matched records from the left table"
Set-Alias FullJoin Join-Object; Set-Alias FullJoin-Object Join-Object -Description "Returns all records when there is a match in either left or right table"
句法
<Object[]> | InnerJoin|LeftJoin|RightJoin|FullJoin <Object[]> [-On <String>|<Array>|<ScriptBlock>] [-Merge <HashTable>|<ScriptBlock>] [-Eq <String>]
InnerJoin|LeftJoin|RightJoin|FullJoin <Object[]>,<Object[]> [-On <String>|<Array>|<ScriptBlock>] [-Merge <HashTable>|<ScriptBlock>] [-Eq <String>]
InnerJoin|LeftJoin|RightJoin|FullJoin -LeftTable <Object[]> -RightTable <Object[]> [-On <String>|<Array>|<ScriptBlock>] [-Merge <HashTable>|<ScriptBlock>] [-Eq <String>]
命令
这个
连接对象
(别名)
加入
)函数是一个具有多个别名的函数,用于联接两个表(每个表由一个
PSCustomObjects
)类似于各自的
SQL Join
指令。默认值
join type
是一个
InnerJoin
.
-
InnerJoin-Object
(别名)
内连接
)
返回两个表中具有匹配值的记录。
-
LeftJoin-Object
(别名)
LeftJoin
)
返回左表中的所有记录和右表中匹配的记录。
-
RightJoin-Object
(别名)
RightJoin
)
返回右表中的所有记录和右表中匹配的记录。
-
FullJoin-Object
(别名)
FullJoin
)
当左表或右表中有匹配项时返回所有记录。
笔记
-
所有
加入
命令与PowerShell版本2及更高版本兼容。
参数
-LeftTable <Object[]>
和
-RightTable <Object[]>
这个
-LeftTable
和
RightTable
参数定义要联接的左表和右表。提供这些表有三种可能的语法:
-
使用PowerShell管道:
<LeftTable>
| Join
<RightTable>
-
在第一个参数位置提供数组中的两个表(用逗号分隔):
Join
<LeftTable>
,
<RightTable>
-
为两个表提供命名参数:
Join -Left
<LeftTable>
-Right
<RightTable>
笔记
-
如果只提供一个表(
Join
<Table>
一个自我
self-join
将在桌子上执行。
-On <String>|<Array>|<ScriptBlock>
和
-Equals <String>
这个
-On
(别名)
Using
)参数定义条件,该条件指定如何联接表以及要在(内部)结果集中包括哪些行。这个
-关于
参数支持以下格式:
-
String -Equals <String>
如果
-关于
值是
String
以及
-等于<字符串>
提供了参数,左列中的属性由
-关于
值需要等于由定义的右列中的属性
-equals
要包含在(内部)结果集中的值。
-
弦
或
Array
如果值是
字符串
或
数组
这个
-关于
参数类似于SQL
using
条款。这意味着所有列出的属性都需要相等(在左侧和右侧),才能包含在(内部)结果集中。默认情况下,列出的属性将输出单个值(另请参见
-Expressions
)
-
ScriptBlock
任何条件表达式,其中
$Left
定义左行,
$Right
定义右行。
笔记
-
这个
脚本块
类型具有最大的比较可能性,但比其他类型慢得多。
-
如果
-关于
参数被省略或来自未知类型,a
cross-join
将执行。
-Merge <HashTable>|<ScriptBlock>
定义如何合并具有相同名称的特定列。这个
-Merge
参数接受类型:a
HashTable
包含每列的特定合并表达式或
脚本块
包含未定义合并表达式的所有列的默认合并表达式。
表达式中的位置:
-
$_
保留每个列名称。
-
左转
保持左边一排
$右
保持右行。
-
$Left.$_
保留每个左值和
$Right.$_
保留每个正确的值。
-
$LeftIndex
保留当前的左行索引和
$RightIndex
保留当前右行索引。
笔记:
-
只有当两个左值都为(
Left.$_
)和正确的价值(
左
)存在(包括
$Null
)否则只返回退出值。
-
如果没有为列定义表达式,则表达式
{$Left.$_, $Right.$_}
使用。
这意味着两个值(在数组中)都被分配给当前属性。
-
由定义的列的表达式
-On <String>
,
-等于<字符串>
和-
<Array>
是:
{$Left.$_}
并且只能由哈希表中定义的列特定表达式覆盖。
这意味着单个值(或者
左转
或
$右
不等于
$null
)分配给当前属性。
-
使用列特定表达式
和
定义默认表达式使用零长度的键名称作为默认表达式,例如
-Merge @{"" = {$Left.$_}; "Column Name" = {$Right.$_}}
实例
给出下表:
$Employee $Department
+---------+---------+-------------+ +-------------+---------+---------+
| Name | Country | Department | | Name | Country | Manager |
+---------+---------+-------------+ +-------------+---------+---------+
| Aerts | Belgium | Sales | | Engineering | Germany | Meyer |
| Bauer | Germany | Engineering | | Marketing | England | Morris |
| Cook | England | Sales | | Sales | France | Millet |
| Duval | France | Engineering | +-------------+---------+---------+
| Evans | England | Marketing |
| Fischer | Germany | Engineering |
+---------+---------+-------------+
PS C:\> # InnerJoin on Department = Name
PS C:\> $Employee | InnerJoin $Department Department -eq Name | Format-Table
Department Name Manager Country
---------- ---- ------- -------
Sales Aerts Millet {Belgium, France}
Engineering Bauer Meyer {Germany, Germany}
Sales Cook Millet {England, France}
Engineering Duval Meyer {France, Germany}
Marketing Evans Morris {England, England}
Engineering Fischer Meyer {Germany, Germany}
PS C:\> # LeftJoin using country (selecting Department.Name and Department.Country)
PS C:\> $Employee | LeftJoin ($Department | Select Manager,Country) Country | Format-Table
Department Name Manager Country
---------- ---- ------- -------
Engineering Bauer Meyer Germany
Sales Cook Morris England
Engineering Duval Millet France
Marketing Evans Morris England
Engineering Fischer Meyer Germany
Sales Aerts Belgium
PS C:\> # InnerJoin on Employee.Department = Department.Name and Employee.Country = Department.Country (returning only the left name and - country)
PS C:\> $Employee | InnerJoin $Department {$Left.Department -eq $Right.Name -and $Left.Country -eq $Right.Country} {$Left.$_}
Department Name Manager Country
---------- ---- ------- -------
Engineering Bauer Meyer Germany
Marketing Evans Morris England
Engineering Fischer Meyer Germany
PS C:\> # Cross Join
PS C:\> $Employee | InnerJoin $Department | Format-Table
Department Name Manager Country
---------- ---- ------- -------
Sales {Aerts, Engineering} Meyer {Belgium, Germany}
Sales {Aerts, Marketing} Morris {Belgium, England}
Sales {Aerts, Sales} Millet {Belgium, France}
Engineering {Bauer, Engineering} Meyer {Germany, Germany}
Engineering {Bauer, Marketing} Morris {Germany, England}
Engineering {Bauer, Sales} Millet {Germany, France}
Sales {Cook, Engineering} Meyer {England, Germany}
Sales {Cook, Marketing} Morris {England, England}
Sales {Cook, Sales} Millet {England, France}
Engineering {Duval, Engineering} Meyer {France, Germany}
Engineering {Duval, Marketing} Morris {France, England}
Engineering {Duval, Sales} Millet {France, France}
Marketing {Evans, Engineering} Meyer {England, Germany}
Marketing {Evans, Marketing} Morris {England, England}
Marketing {Evans, Sales} Millet {England, France}
Engineering {Fischer, Engineering} Meyer {Germany, Germany}
Engineering {Fischer, Marketing} Morris {Germany, England}
Engineering {Fischer, Sales} Millet {Germany, France}
更新服务列表(替换名称上的现有服务并添加新服务)
Import-CSV .\Svc.csv | LeftJoin (Get-Service) Name {$Right.$_} | Export-CSV .\Svc.csv
更新进程列表,仅插入具有较高CPU的进程
Import-CSV .\CPU.csv | LeftJoin (Get-Process) ID {If ($Left.CPU -gt $Right.CPU) {$Left.$_} Else {$Right.$_}} | Export-CSV .\CPU.csv
最新的
连接对象
版本,请参见
PowerShell Gallery
或项目现场:
https://github.com/iRon7/Join-Object
