SPL：多对一连接
连接（JOIN）用于把来自两个或多个表的记录结合起来。本文将探讨对于连接问题，SPL 的解决方案和基本原理。
表之间存在的数据相互依赖关系，就叫做表间关联关系。表间关联关系可以分为以下几种：一对一、多对一、一对多和多对多。我们可以通过表间关联关系，把两个或多个表连接起来，从而实现多表关联查询的目的。
多对一关联，是指一张表的任意条记录能与另外一张表的一条记录进行对应。多对一关联关系，常见于有外键关系的两个表。表 A 的某些字段与表 B 的主键关联。A 表中与 B 表主键关联的字段称为指向 B 表的外键，B 也称为 A 的外键表。
例如有员工表和部门表，其中员工表的部门 ID 字段，指向了部门表的 ID 字段。每个员工都有一个部门，但是每个部门可以有多个员工，员工表和部门表是多对一的关系。
我们经常把一对多和多对一混在一起来讲，其实是受了 SQL 的误导。在 SQL 中对这两种关联关系并没有进行区分，也没有单独提供各自的解决方案。
解决多对一关联的思路与一对多完全不同，我们可以在连接时将外键值转换成对应记录，或者将需要的字段值附加到“多”表上，然后就可以当作是一张表进行查询运算了。SPL 提供了 A.switch()，A.join() 等函数，针对外键表连接（多对一）进行优化和提速。
一．外键对象化
【例 1】 根据员工表和部门表，查询所有员工的姓名及其所在部门名称。部分数据如下：
EMPLOYEE:
ID |
NAME |
BIRTHDAY |
DEPARTMENTID |
SALARY |
1 |
Rebecca |
1974/11/20 |
6 |
7000 |
2 |
Ashley |
1980/07/19 |
2 |
11000 |
3 |
Rachel |
1970/12/17 |
7 |
9000 |
4 |
Emily |
1985/03/07 |
3 |
7000 |
5 |
Ashley |
1975/05/13 |
6 |
16000 |
… |
… |
… |
… |
… |
DEPARTMENT:
ID |
NAME |
MANAGER |
1 |
Administration |
18 |
2 |
Finance |
2 |
3 |
HR |
4 |
4 |
Marketing |
6 |
5 |
Production |
7 |
… |
… |
… |
SPL提供了函数 A.switch()，将编码字段的键值转换为对应的记录。
SPL脚本如下：
A |
|
1 |
=T("Employee.txt") |
2 |
=T("Department.txt") |
3 |
=A1.switch(DEPARTMENTID, A2:ID) |
4 |
=A3.new(NAME, DEPARTMENTID.NAME:DEPT_NAME) |
A1：导入员工表。
A2：导入部门表。
A3：使用函数 A.switch() 将部门 ID 进行外键对象化，即将部门 ID 的字段值转换成相应的部门记录。
A4：返回员工名称和部门名称，其中部门名称可以通过部门记录的 NAME 字段获得。
经过外键对象化以后，员工表的部门字段的值，就是员工在部门表中的对应记录。在员工表中可以直接用”部门. 名称”来获取部门名称，也可以用”部门. 经理”来获取部门经理名称等等。
由于这个例子比较简单，可能有人会有疑问，在连接时将需要的外键字段引入就可以了，为什么还要进行外键对象化呢？接下来我们用一个稍微复杂一点的例子来说明。
【例 2】 根据员工表和部门表，查询哪些美国籍员工有一个中国籍经理。部分数据如下：
EMPLOYEE:
ID |
NAME |
BIRTHDAY |
DEPARTMENTID |
SALARY |
1 |
Rebecca |
1974/11/20 |
6 |
7000 |
2 |
Ashley |
1980/07/19 |
2 |
11000 |
3 |
Rachel |
1970/12/17 |
7 |
9000 |
4 |
Emily |
1985/03/07 |
3 |
7000 |
5 |
Ashley |
1975/05/13 |
6 |
16000 |
… |
… |
… |
… |
… |
DEPARTMENT:
ID |
NAME |
MANAGER |
1 |
Administration |
18 |
2 |
Finance |
2 |
3 |
HR |
4 |
4 |
Marketing |
6 |
5 |
Production |
7 |
… |
… |
… |
我们还是可以通过函数 A.switch() 进行外键的对象化来解决问题。
SPL脚本如下：
A |
|
1 |
=T("Employee.txt").keys(ID) |
2 |
=T("Department.txt").keys(ID) |
3 |
=A2.switch(MANAGER, A1) |
4 |
=A1.switch(DEPARTMENTID, A2) |
5 |
=A4.select(NATION=="American" && DEPARTMENTID.MANAGER.NATION=="Chinese") |
A1：导入员工表，以 ID 为主键。
A2：导入部门表，以 ID 为主键。
A3：使用函数 A.switch() 外键对象化，将部门表的经理字段，转换成相应的员工记录。
A4：使用函数 A.switch() 外键对象化，将员工表的部门字段，转换成相应的部门记录。
A5：选出经理是中国籍的美国籍员工。
这个例子中多了一层关联关系，但是 SPL 语句并没有变得复杂，只是按照逻辑增加了一次外键对象化。我们不需要每次外键连接都提前想好需要哪些字段，可以直接从记录对象中取得，在选出时也非常符合自然逻辑，部门的经理的国籍（DEPARTMENTID.MANAGER.NATION）是中国籍。
【例 3】 根据课程表和选课表，查询有多少学生选修了“Matlab”课程。部分数据如下：
COURSE：
ID |
NAME |
TEACHERID |
1 |
Environmental protection and sustainable development |
5 |
2 |
Mental health of College Students |
1 |
3 |
Matlab |
8 |
4 |
Electromechanical basic practice |
7 |
5 |
Introduction to modern life science |
3 |
… |
… |
… |
SELECT_COURSE：
ID |
COURSEID |
STUDENTID |
1 |
6 |
59 |
2 |
6 |
43 |
3 |
5 |
52 |
4 |
5 |
44 |
5 |
5 |
37 |
… |
… |
… |
我们想要在连接时，删除不匹配的记录（选出课程不是 Matlab 的），可以使用内连接。在函数 A.switch() 中，选项 @i 用于在连接时删除不匹配的记录。
SPL脚本如下：
A |
|
1 |
=T("Course.csv") |
2 |
=T("SelectCourse.csv") |
3 |
=A1.select(NAME:"Matlab") |
4 |
=A2.switch@i(COURSEID, A3:ID).count() |
A1：导入课程表。
A2：导入选课表。
A3：在课程表中选出名为 Matlab 的记录。
A4：使用函数 A.switch() 的选项 @i，在连接时删除不匹配的记录，即剩下的都是选择了 Matlab 课程的记录，再统计数量。
【例 4】 根据销售表和客户表，查询 2014 年新增客户的销售情况（即销售表的客户 ID 不在客户表中的记录）。部分数据如下：
SALES：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
10248 |
VINET |
2013/7/4 |
5 |
59 |
2440 |
10249 |
TOMSP |
2013/7/5 |
6 |
38 |
1863.4 |
10250 |
HANAR |
2013/7/8 |
4 |
65 |
1813 |
10251 |
VICTE |
2013/7/8 |
3 |
66 |
670.8 |
10252 |
SUPRD |
2013/7/9 |
4 |
46 |
3730 |
… |
… |
… |
… |
… |
… |
CUSTOMER：
ID |
NAME |
CITY |
POSTCODE |
TEL |
ALFKI |
Sanchuan Industrial Co., Ltd |
Tianjin |
343567 |
(030) 30074321 |
ANATR |
Southeast industries |
Tianjin |
234575 |
(030) 35554729 |
ANTON |
Tanson trade |
Shijiazhuang |
985060 |
(0321) 5553932 |
AROUT |
Guoding Co., Ltd |
Shenzhen |
890879 |
(0571) 45557788 |
BERGS |
Tongheng machinery |
Nanjing |
798089 |
(0921) 9123465 |
… |
… |
… |
… |
… |
我们想要在连接时，只保留不匹配的记录（销售表的客户 ID 不在客户表中的）。在函数 A.switch() 中，选项 @d 时只保留不匹配的记录。
SPL脚本如下：
A |
|
1 |
=T("Sales.csv") |
2 |
=T("Customer.txt") |
3 |
=A1.select(year(ORDERDATE)==2014) |
4 |
=A3.switch@d(CUSTOMERID, A2:ID) |
A1：导入销售表。
A2：导入客户表。
A3：在销售表中选出 2014 的记录。
A4：使用函数 A.switch() 的选项 @d，在连接时只保留不匹配的记录，即 2014 年新增客户的销售记录。
【例 5】 根据组织机构表，查询每个机构的上级机构名称。部分数据如下：
ID |
ORG_NAME |
PARENT_ID |
1 |
Head Office |
0 |
2 |
Beijing Branch Office |
1 |
3 |
Shanghai Branch Office |
1 |
4 |
Chengdu Branch Office |
1 |
5 |
Beijing R&D Center |
2 |
… |
… |
… |
在组织机构表中，上级机构 ID（PARENT_ID）指向的还是本表，这是一个自连接的例子。我们还是可以使用函数 A.switch() 进行外键对象化，外键表选择自身即可。
SPL脚本如下：
A |
|
1 |
=T("Organization.txt") |
2 |
=A1.switch(PARENT_ID, A1:ID) |
3 |
=A2.new(ID, ORG_NAME, PARENT_ID.ORG_NAME:PARENT_NAME) |
A1：导入组织机构表。
A2：使用函数 A.switch() 进行外键对象化，将父机构 ID 字段值转换为相应的父机构记录。
A3：返回所有机构的名称和父机构的名称。
二．外键对象化不支持的情况
多字段外键时，外键对象化就不支持了，这种情况可以通过增加外键表的字段或者记录的方式来解决。
【例 6】 根据组织机构表，选出 2014 年回款的价格超过 500 的产品名称。表间关系如下：
上图中，订单明细表和产品表通过单字段外键产品序号进行关联，订单回款表与订单明细表通过订单编号、订单序号这两个外键字段进行关联。多字段外键时，SPL 提供了函数 A.join() 用于连接外键表，将外键表的字段值或者记录添加到新增的字段中。解决方案：第一步，订单明细表的订购产品字段进行外键对象化，替换为产品表的相应记录。第二步，订单回款表与订单明细表进行连接，相应的记录存储在新增的字段 DETAIL 中。
SPL脚本如下：
A |
|
1 |
=T("OrderPayment.txt") |
2 |
=T("OrderDetail.txt") |
3 |
=T("Product.txt") |
4 |
=A2.switch(PRODUCTID, A3:ID) |
5 |
=A1.join(ORDER:ORDER_NUMBER,A2:ID:ORDER_NUMBER,~:DETAIL) |
6 |
=A5.select(year(PAY_DATE)==2014 && DETAIL.PRICE>500) |
7 |
=A6.new(ORDER,PAY_DATE,DETAIL.PRODUCTID.NAME:PRODUCT_NAME,DETAIL.PRICE:PRICE) |
A1：导入订单回款表。
A2：导入订单明细表。
A3：导入产品表。
A4：使用函数 A.switch() 进行外键对象化，将产品 ID 字段值转换为相应的产品记录。
A5：使用函数 A.join() 通过双字段连接订单回款表和订单明细表，将订单明细表的相应记录添加到 DETAIL 字段。
A6：选出 2014 年汇款价格超过 500 的记录。
A7：产生字段为订单编号、回款日期、产品名称、产品价格的表。
当引用的外键表字段可能为空的情况下，我们又想要保留外键字段的原值时，就不能使用外键对象化了。可以使用函数 A.join() 连接外键表，将外键表的字段值添加到新增的字段中。
【例 7】 根据销售表和客户表，查询 2014 年每个客户的总销售额（有的客户可能还没有登记在客户表中）。部分数据如下：
SALES：
ID |
CUSTOMERID |
ORDERDATE |
SELLERID |
PRODUCTID |
AMOUNT |
10248 |
VINET |
2013/7/4 |
5 |
59 |
2440 |
10249 |
TOMSP |
2013/7/5 |
6 |
38 |
1863.4 |
10250 |
HANAR |
2013/7/8 |
4 |
65 |
1813 |
10251 |
VICTE |
2013/7/8 |
3 |
66 |
670.8 |
10252 |
SUPRD |
2013/7/9 |
4 |
46 |
3730 |
… |
… |
… |
… |
… |
… |
CUSTOMER：
ID |
NAME |
CITY |
POSTCODE |
TEL |
ALFKI |
Sanchuan Industrial Co., Ltd |
Tianjin |
343567 |
(030) 30074321 |
ANATR |
Southeast industries |
Tianjin |
234575 |
(030) 35554729 |
ANTON |
Tanson trade |
Shijiazhuang |
985060 |
(0321) 5553932 |
AROUT |
Guoding Co., Ltd |
Shenzhen |
890879 |
(0571) 45557788 |
BERGS |
Tongheng machinery |
Nanjing |
798089 |
(0921) 9123465 |
… |
… |
… |
… |
… |
SPL脚本如下：
A |
|
1 |
=T("Sales.csv") |
2 |
=T("Customer.txt") |
3 |
=A1.select(year(ORDERDATE)==2014) |
4 |
=A3.join(CUSTOMERID, A2:ID, NAME:CUSTOMERNAME) |
5 |
=A4.groups(if (CUSTOMERNAME,CUSTOMERNAME,CUSTOMERID):CUSTOMER; sum(AMOUNT):AMOUNT) |
A1：导入销售表。
A2：导入客户表。
A3：选出 2014 年的销售记录。
A4：使用函数 A.join() 连接销售表和客户表，将客户表的客户名称字段添加到销售表的 CUSTOMERNAME 字段。
A5：按照客户名称分组汇总每个客户的总销售额，当客户名称不存在时使用客户 ID。
通过以上的例子我们可以看到，多对一与一对多关系是有很大的区别的。在 SPL 中，明确的区分了两者的不同，分别提供了不同的解决方案来支持多对一和一对多两种关联关系。
英文版