行式数据求交并差

zoomafia

举例例1：对关键列求交并差

有Excel文件Book1.xlsx，数据如下所示：

team	member1	member2	member3	member4	member5
9	Taylor	Stephanie	Stephanie	David	Amanda
7	Ian	Angela	Brooke	Steven	Timothy
5	Nicholas	Michael	Madeline	Antony	Logan
3	Sophia	Jacob	Andrew	Alexis	Brianna
6	Christopher	Alyssa	Benjamin	Thomas	Courtney
1	Hannah	Isabella	Abigail	Logan	Mary
2	Benjamin	Stephanie	Jennifer	Jacob	Jose
10	Katherine	Jonathan	Kayla	Isabella	Luis
8	Daniel	Justin	Dylan	Amanda	Hailey
4	Kayla	Hailey	Jennifer	Austin	Matthew

有Excel文件Book2.xlsx，数据如下所示：

team	member1	member2	member3	member4	member5
10	Daniel	Kayla	Sarah	Luis	Ryan
15	Abigail	Hailey	Antony	Madison	Timothy
11	Haley	Stephanie	Justin	Joseph	Bryan
13	Timothy	Joseph	Hailey	Katherine	Lauren
12	Ryan	David	Isabella	Julia	Jose
9	Julia	Haley	Elizabeth	Julia	Alyssa
14	Jose	Christian	Claire	Dylan	Jose
8	Christian	Lauren	Justin	Austin	Chloe

两份数据按team列求交、并、差。

编写SPL脚本：

	A
1	=file("Book1.xlsx").xlsimport@t()
2	=file("Book2.xlsx").xlsimport@t()
3	=[A1,A2].merge@ou(team)
4	=[A1,A2].merge@oi(team)
5	=[A1,A2].merge@od(team)

A1 读取Excel数据

A2 读取Excel数据

A3 按team列并，结果如下：

team	member1	member2	member3	member4	member5
9	Taylor	Stephanie	Stephanie	David	Amanda
7	Ian	Angela	Brooke	Steven	Timothy
5	Nicholas	Michael	Madeline	Antony	Logan
3	Sophia	Jacob	Andrew	Alexis	Brianna
6	Christopher	Alyssa	Benjamin	Thomas	Courtney
1	Hannah	Isabella	Abigail	Logan	Mary
2	Benjamin	Stephanie	Jennifer	Jacob	Jose
10	Katherine	Jonathan	Kayla	Isabella	Luis
8	Daniel	Justin	Dylan	Amanda	Hailey
4	Kayla	Hailey	Jennifer	Austin	Matthew
15	Abigail	Hailey	Antony	Madison	Timothy
11	Haley	Stephanie	Justin	Joseph	Bryan
13	Timothy	Joseph	Hailey	Katherine	Lauren
12	Ryan	David	Isabella	Julia	Jose
14	Jose	Christian	Claire	Dylan	Jose

A4 按team列交，结果如下：

team	member1	member2	member3	member4	member5
9	Taylor	Stephanie	Stephanie	David	Amanda
10	Katherine	Jonathan	Kayla	Isabella	Luis
8	Daniel	Justin	Dylan	Amanda	Hailey

A5 按team列差，结果如下：

team	member1	member2	member3	member4	member5
7	Ian	Angela	Brooke	Steven	Timothy
5	Nicholas	Michael	Madeline	Antony	Logan
3	Sophia	Jacob	Andrew	Alexis	Brianna
6	Christopher	Alyssa	Benjamin	Thomas	Courtney
1	Hannah	Isabella	Abigail	Logan	Mary
2	Benjamin	Stephanie	Jennifer	Jacob	Jose
4	Kayla	Hailey	Jennifer	Austin	Matthew

zoomafia

例2：对整行求交并差

有Excel文件Book1.xlsx，数据如下所示：

team	member1	member2	member3	member4	member5
9	Taylor	Stephanie	Stephanie	David	Amanda
7	Ian	Angela	Brooke	Steven	Timothy
5	Nicholas	Michael	Madeline	Antony	Logan
3	Sophia	Jacob	Andrew	Alexis	Brianna
6	Christopher	Alyssa	Benjamin	Thomas	Courtney
1	Hannah	Isabella	Abigail	Logan	Mary
2	Benjamin	Stephanie	Jennifer	Jacob	Jose
10	Katherine	Jonathan	Kayla	Isabella	Luis
8	Daniel	Justin	Dylan	Amanda	Hailey
4	Kayla	Hailey	Jennifer	Austin	Matthew

有Excel文件Book2.xlsx，数据如下所示：

team	member1	member2	member3	member4	member5
10	Daniel	Kayla	Sarah	Luis	Ryan
15	Abigail	Hailey	Antony	Madison	Timothy
11	Haley	Stephanie	Justin	Joseph	Bryan
13	Timothy	Joseph	Hailey	Katherine	Lauren
12	Ryan	David	Isabella	Julia	Jose
9	Julia	Haley	Elizabeth	Julia	Alyssa
14	Jose	Christian	Claire	Dylan	Jose
8	Christian	Lauren	Justin	Austin	Chloe

两份数据按整行求交、并、差。

编写SPL脚本：

	A
1	=file("Book1.xlsx").xlsimport@t()
2	=file("Book2.xlsx").xlsimport@t()
3	=[A1,A2].merge@ou()
4	=[A1,A2].merge@oi()
5	=[A1,A2].merge@od()

A1 读取Excel数据

A2 读取Excel数据

A3 按整行并，结果如下：

team	member1	member2	member3	member4	member5
9	Taylor	Stephanie	Stephanie	David	Amanda
7	Ian	Angela	Brooke	Steven	Timothy
5	Nicholas	Michael	Madeline	Antony	Logan
3	Sophia	Jacob	Andrew	Alexis	Brianna
6	Christopher	Alyssa	Benjamin	Thomas	Courtney
1	Hannah	Isabella	Abigail	Logan	Mary
2	Benjamin	Stephanie	Jennifer	Jacob	Jose
10	Katherine	Jonathan	Kayla	Isabella	Luis
8	Daniel	Justin	Dylan	Amanda	Hailey
4	Kayla	Hailey	Jennifer	Austin	Matthew
10	Daniel	Kayla	Sarah	Luis	Ryan
15	Abigail	Hailey	Antony	Madison	Timothy
11	Haley	Stephanie	Justin	Joseph	Bryan
13	Timothy	Joseph	Hailey	Katherine	Lauren
12	Ryan	David	Isabella	Julia	Jose
9	Julia	Haley	Elizabeth	Julia	Alyssa
14	Jose	Christian	Claire	Dylan	Jose
8	Christian	Lauren	Justin	Austin	Chloe

A4 按整行交，因为两份数据没有相同记录，结果为空。

A5 按整行差，结果如下：

team	member1	member2	member3	member4	member5
9	Taylor	Stephanie	Stephanie	David	Amanda
7	Ian	Angela	Brooke	Steven	Timothy
5	Nicholas	Michael	Madeline	Antony	Logan
3	Sophia	Jacob	Andrew	Alexis	Brianna
6	Christopher	Alyssa	Benjamin	Thomas	Courtney
1	Hannah	Isabella	Abigail	Logan	Mary
2	Benjamin	Stephanie	Jennifer	Jacob	Jose
10	Katherine	Jonathan	Kayla	Isabella	Luis
8	Daniel	Justin	Dylan	Amanda	Hailey
4	Kayla	Hailey	Jennifer	Austin	Matthew