不确定数量的集合求交并差
举例
有多个Excel文件Book1.xlsx，Book2.xlsx，Book3.xlsx，…，这些文件的命名规则是Book[N].xlsx，N是从1开始的数，文件中的数据如下所示：
Book1.xlsx
team |
member1 |
member2 |
member3 |
member4 |
member5 |
2 |
Nicholas |
Kaitlyn |
Hailey |
David |
Christopher |
1 |
Alexis |
Cole |
Justin |
Benjamin |
Natalie |
5 |
Logan |
Dylan |
Elizabeth |
Jose |
John |
3 |
Isabella |
Nicholas |
Angela |
Daniel |
Alexander |
4 |
Madison |
Nicholas |
Luis |
Hannah |
John |
Book2.xlsx
team |
member1 |
member2 |
member3 |
member4 |
member5 |
4 |
Madison |
Nicholas |
Luis |
Hannah |
John |
8 |
Morgan |
Jose |
Joseph |
Cameron |
Destiny |
9 |
Tyler |
Jessica |
Elizabeth |
Alyssa |
Destiny |
6 |
Robert |
John |
Brandon |
Jacob |
Hailey |
7 |
Abigail |
Sarah |
Hailey |
Sydney |
Joseph |
5 |
Logan |
Dylan |
Elizabeth |
Jose |
John |
10 |
John |
Victoria |
Brandon |
Victoria |
Katherine |
Book3.xlsx
team |
member1 |
member2 |
member3 |
member4 |
member5 |
10 |
John |
Victoria |
Brandon |
Victoria |
Katherine |
11 |
Dylan |
Ian |
Jose |
Antony |
Rebecca |
12 |
Nathan |
Austin |
Logan |
Michael |
Kaitlyn |
13 |
Jennifer |
Matthew |
Samantha |
Noah |
Olivia |
9 |
Tyler |
Jessica |
Elizabeth |
Alyssa |
Destiny |
8 |
Morgan |
Jose |
Joseph |
Cameron |
Destiny |
Book4.xlsx
…
求所有Book[N].xlsx中数据的交、并、差。
编写SPL脚本：
A |
|
1 |
=directory("book*.xlsx") |
2 |
=A1.(file(~).xlsimport@t()) |
3 |
=A2.merge@ou(team) |
4 |
=A2.merge@oi(team) |
5 |
=A2.merge@od(team) |
A1 按文件名序，列出所有Book[N].xlsx文件名
A2 依次读取各Excel文件中的数据，多份数据组成一个集合
A3 多份数据，按team列并，结果如下：
team |
member1 |
member2 |
member3 |
member4 |
member5 |
2 |
Nicholas |
Kaitlyn |
Hailey |
David |
Christopher |
1 |
Alexis |
Cole |
Justin |
Benjamin |
Natalie |
5 |
Logan |
Dylan |
Elizabeth |
Jose |
John |
3 |
Isabella |
Nicholas |
Angela |
Daniel |
Alexander |
4 |
Madison |
Nicholas |
Luis |
Hannah |
John |
8 |
Morgan |
Jose |
Joseph |
Cameron |
Destiny |
9 |
Tyler |
Jessica |
Elizabeth |
Alyssa |
Destiny |
6 |
Robert |
John |
Brandon |
Jacob |
Hailey |
7 |
Abigail |
Sarah |
Hailey |
Sydney |
Joseph |
10 |
John |
Victoria |
Brandon |
Victoria |
Katherine |
11 |
Dylan |
Ian |
Jose |
Antony |
Rebecca |
12 |
Nathan |
Austin |
Logan |
Michael |
Kaitlyn |
13 |
Jennifer |
Matthew |
Samantha |
Noah |
Olivia |
15 |
Samantha |
Ian |
Katherine |
Alexander |
Joshua |
14 |
Abigail |
Antony |
Hailey |
Rachel |
William |
A4 多份数据，按team列交，结果为空。
A5 多份数据，按team列差，结果如下：
team |
member1 |
member2 |
member3 |
member4 |
member5 |
2 |
Nicholas |
Kaitlyn |
Hailey |
David |
Christopher |
1 |
Alexis |
Cole |
Justin |
Benjamin |
Natalie |
3 |
Isabella |
Nicholas |
Angela |
Daniel |
Alexander |
英文已更新