我正在连接两个表,这两个表都有数百个名称相似的列。我想更改每个表中的所有列名,以包含表名。为了简化查询,我不想显式调用每个列名。有没有一种方法可以在不显式调用每一列的情况下将表名附加到所有列名中?
例如:
let T1 = datatable (Key:string , Col2:string , Col3:string )[
"1", "b", "c",
"2", "e", "f",
"3", "h", "i"];
let T2 = datatable (Key:string , Col2:string , Col3:string )[
"1", "B", "C",
"2", "E", "F",
"4", "H", "I"];
T1 | join T2 on Key
结果:
Key Col2 Col3 Key1 Col21 Col31
1 b c 1 B C
2 e f 2 E F
期望结果:
T1.Key T1.Col2 T1.Col3 T2.Key T2.Col2 T2.Col3
1 b c 1 B C
2 e f 2 E F
如果列的顺序对您来说不重要,那么这将是一种方法:
let T1 = datatable (Key:string , Col2:string , Col3:string )
[
"1", "b", "c",
"2", "e", "f",
"3", "h", "i"
]
| project PackedRecord = todynamic(replace_regex(tostring(pack_all()), '"([a-zA-Z0-9_]*)":"', @'"T1_1":"'))
| evaluate bag_unpack(PackedRecord);
let T2 = datatable (Key:string , Col2:string , Col3:string )
[
"1", "B", "C",
"2", "E", "F",
"4", "H", "I"
]
| project PackedRecord = todynamic(replace_regex(tostring(pack_all()), '"([a-zA-Z0-9_]*)":"', @'"T2_1":"'))
| evaluate bag_unpack(PackedRecord);
let JoinTable = T1 | join kind=inner T2 on $left.T1_Key == $right.T2_Key;
JoinTable
结果:
T1_Col2 | T1_Col3 | T1_KeyT2_Col2T2_Col3T2_Key|||||
---|---|---|---|---|---|---|
b | c | 1 | b | c | 1 | |
f | 2 | e | f | 2