不确定数量的集合求交并差

zoomafia

举例

有多个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