行式数据求交并差
举例
例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 |
例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 |
英文已更新