不确定数量的集合求交并差
举例
有多个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 |
英文已更新