SQL 和 SPL 的一对一和一对多连接对比
【摘要】
连接（JOIN）用于把来自两个或多个表的记录结合起来。SQL 和 SPL 是大家比较熟悉的程序语言，本文将探讨对于连接问题，这两种语言的解决方案和基本原理。如何简便快捷的处理连接运算，这里为你全程解析，并提供 SQL 和 SPL 示例代码。SQL 和 SPL 的一对一和一对多连接对比
表之间存在的数据相互依赖关系，就叫做表间关联关系。表间关联关系可以分为以下几种：一对一、多对一、一对多和多对多。我们可以通过表间关联关系，把两个或多个表连接起来，从而实现多表关联查询的目的。
一． 一对一
一对一关联，是指一张表的一条记录只能与另外一张表的一条记录进行对应；反之亦然。一对一关联关系常见于两张表都使用相同的主键字段，这样两张表的记录就是一一对应的关系。
例如有学生表和学生联系表，两个表的主键都是学生 ID。第一张表中存储了学生的姓名、性别、生日、系和班级等等基本信息，而学生联系表中存储了学生的联系人和地址等联系方式。每一个学生信息都对应了唯一一条联系方式，每一个联系方式也对应了唯一的学生信息。
【例 1】 根据学生表和学生联系表，查询有联系方式的学生姓名、联系人和住址。部分数据如下：
STUDENT:
ID |
NAME |
DEPARTMENTID |
CLASSID |
GENDER |
BIRTHDAY |
1 |
Rebecca |
1 |
1 |
F |
2010/09/08 |
2 |
Ashley |
1 |
1 |
F |
2010/10/09 |
3 |
Rachel |
1 |
1 |
F |
2011/04/29 |
4 |
Emily |
1 |
1 |
F |
2010/11/24 |
5 |
Ashley |
1 |
1 |
F |
2011/03/03 |
… |
… |
… |
… |
… |
… |
STUDENT_CONTACTS:
ID |
CONTACTS |
ADDRESS |
1 |
Mrs. Moore |
124 Guangming North Road |
2 |
Mrs. Wilson |
116 Baishi Road |
3 |
Mr. Johnson |
No.8, Mingcheng Road, Haidian District |
4 |
Mr. Smith |
12 Fuxing Road |
5 |
Mr. Smith |
462 Shijingshan Road |
… |
… |
… |
SQL的解决方案：
根据题意，有的学生可能没有登记联系方式。要查询满足条件的学生姓名和联系方式，我们需要使用内连接，只有学生 ID 在两个表中都存在的记录才会被选出。内连接也叫连接，会从结果表中删除与其他被连接表中没有匹配的所有行。在 SQL 语句中，INNER JOIN 用于内连接前后两张表，ON 语句定义了连接的条件。具体语句如下：
SELECT
S.NAME,C.CONTACTS,C.ADDRESS
FROM
STUDENT S
INNER JOIN
STUDENT_CONTACTS C
ON S.ID=C.ID
SPL的解决方案：
在 SPL 中称这种一对一关联关系的两个（或多个）表互为同维表。SPL 提供了函数 join() 用于连接，默认为内连接。
A |
|
1 |
=T("Student.txt") |
2 |
=T("StudentContacts.txt") |
3 |
=join(A1:S,ID;A2:C,ID) |
4 |
=A3.new(S.NAME,C.CONTACTS,C.ADDRESS) |
A1：从文件中导入学生表。
A2：从文件中导入学生联系表。
A3：学生表和学生联系表通过学生 ID 进行内连接。
A4：创建以学生姓名、联系人、联系地址为字段的序表。
SPL同样也支持从数据库中读取数据表，比如数据来源于数据库"db"中 "STUDENT" 表时， A1可以改为：
A |
|
1 |
=connect("db").query("select * from STUDENT") |
【例 2】 根据员工表和经理表，查询所有员工（包括经理）的收入（加上津贴）。部分数据如下：
EMPLOYEE:
ID |
NAME |
BIRTHDAY |
STATE |
DEPT |
SALARY |
1 |
Rebecca |
1974/11/20 |
California |
R&D |
7000 |
2 |
Ashley |
1980/07/19 |
New York |
Finance |
11000 |
3 |
Rachel |
1970/12/17 |
New Mexico |
Sales |
9000 |
4 |
Emily |
1985/03/07 |
Texas |
HR |
7000 |
5 |
Ashley |
1975/05/13 |
Texas |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
MANAGER:
ID |
ALLOWANCE |
18 |
7000 |
2 |
11000 |
4 |
7000 |
6 |
10000 |
7 |
9000 |
… |
… |
SQL的解决方案：
经理也是员工，只不过经理表中额外存储了一些信息，例如津贴。要查询所有员工的工资，需要使用左连接，所有的员工不管是否经理都会被选出。左连接也叫左外连接，以左表为基础将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出条件与左表满足的部分。在 SQL 语句中，LEFT JOIN 用于左连接。具体语句如下：
SELECT
E.ID,E.NAME,E.SALARY+NVL(M.ALLOWANCE,0) INCOME
FROM EMPLOYEE E
LEFT JOIN
MANAGER M
ON E.ID=M.ID
SPL的解决方案：
SPL提供了函数 join() 用于连接，选项 @1 表示左连接。
A |
|
1 |
=T("Employee.csv") |
2 |
=T("Manager.txt") |
3 |
=join@1(A1:E, ID; A2:M, ID) |
4 |
=A3.new(E.ID, E.NAME, E.SALARY+M.ALLOWANCE:INCOME) |
A1：从文件中导入员工表。
A2：从文件中导入经理表。
A3：员工表和经理表根据员工 ID，以第一个表（员工表）为准进行左连接。
A4：创建以员工 ID、姓名、总收入为字段的序表。
一对一关联关系，在所有关联关系中是最简单的，两个表连接时直接按照主键进行连接即可。无论是 SQL 还是 SPL 都可以很好的解决一对一关联关系。
二． 一对多
一对多关联，是指一张表的一条记录能与另外一张表的任意记录进行对应。在一对多的关系中，我们把“一”的这端表叫做主表，“多”的这端表叫子表（从表）。例如有订单表和订单明细表，每笔订单对应唯一的订单 ID，但是每个订单 ID 可能对应多条订单明细信息。我们称订单表是主表，订单明细表是子表。
【例 3】 根据订单表和订单明细表，统计每个订单的总金额。部分数据如下：
ORDERS:
ID |
CUSTOMERID |
EMPLOYEEID |
ORDER_DATE |
ARRIVAL_DATE |
10248 |
VINET |
5 |
2012/07/04 |
2012/08/01 |
10249 |
TOMSP |
6 |
2012/07/05 |
2012/08/16 |
10250 |
HANAR |
4 |
2012/07/08 |
2012/08/05 |
10251 |
VICTE |
3 |
2012/07/08 |
2012/08/05 |
10252 |
SUPRD |
4 |
2012/07/09 |
2012/08/06 |
… |
… |
… |
… |
… |
ORDER_DETAIL:
ID |
ORDER_NUMBER |
PRODUCTID |
PRICE |
COUNT |
DISCOUNT |
10814 |
1 |
48 |
102.0 |
8 |
0.15 |
10814 |
2 |
48 |
102.0 |
8 |
0.15 |
10814 |
3 |
48 |
306.0 |
24 |
0.15 |
10814 |
4 |
48 |
102.0 |
8 |
0.15 |
10814 |
5 |
48 |
204.0 |
16 |
0.15 |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
在 SQL 中处理一对多关联关系时，还是使用 JOIN 语句。SQL 语句如下：
SELECT
ID, SUM(PRICE*COUNT) AMOUNT
FROM (
SELECT
Orders.ID, Detail.PRICE, Detail.COUNT
FROM ORDERS Orders
INNER JOIN
ORDER_DETAIL Detail
ON Orders.ID=Detail.ID
)
GROUP BY ID
ORDER BY ID
SPL的解决方案：
单个主子表时，可以使用函数 join(), 通过子表的部分主键与主表主键进行连接。
A |
|
1 |
=T("Orders.txt") |
2 |
=T("OrderDetail.txt") |
3 |
=join(A1:Orders,ID; A2:Detail,ID) |
4 |
=A3.groups(Orders.ID; sum(Detail.PRICE*Detail.COUNT):AMOUNT) |
A1：导入订单表。
A2：导入订单明细表。
A3：订单表和订单明细表通过订单 ID 进行连接。
A4：分组汇总每个订单的总金额。
【例 4】 订单表还有一个子表用于记录回款情况。我们现在想知道哪些订单还没有收到全部回款，也就是累计回款金额小于订单总金额的订单。部分数据如下：
ORDERS:
ID |
CUSTOMERID |
EMPLOYEEID |
ORDER_DATE |
ARRIVAL_DATE |
10248 |
VINET |
5 |
2012/07/04 |
2012/08/01 |
10249 |
TOMSP |
6 |
2012/07/05 |
2012/08/16 |
10250 |
HANAR |
4 |
2012/07/08 |
2012/08/05 |
10251 |
VICTE |
3 |
2012/07/08 |
2012/08/05 |
10252 |
SUPRD |
4 |
2012/07/09 |
2012/08/06 |
… |
… |
… |
… |
… |
ORDER_DETAIL:
ID |
ORDER_NUMBER |
PRODUCTID |
PRICE |
COUNT |
DISCOUNT |
10814 |
1 |
48 |
102.0 |
8 |
0.15 |
10814 |
2 |
48 |
102.0 |
8 |
0.15 |
10814 |
3 |
48 |
306.0 |
24 |
0.15 |
10814 |
4 |
48 |
102.0 |
8 |
0.15 |
10814 |
5 |
48 |
204.0 |
16 |
0.15 |
… |
… |
… |
… |
… |
… |
ORDER_PAYMENT:
ID |
PAY_DATE |
AMOUNT |
CHANNEL |
INSTALMENTS |
10814 |
2014/01/05 |
816.0 |
3 |
0 |
10848 |
2014/01/23 |
800.25 |
2 |
1 |
10848 |
2014/01/23 |
800.25 |
0 |
0 |
10848 |
2014/01/23 |
800.25 |
3 |
1 |
10966 |
2014/03/20 |
572.0 |
2 |
1 |
… |
… |
… |
… |
… |
SQL的解决方案：
简单地把这三个表 JOIN 起来是不对的，订单明细和订单回款表会发生多对多的关系。子表按订单 ID 分组后，就变成了具有唯一性的事实上的主键，多表之间就可以直接用订单 ID 进行连接了。
SELECT
Orders.ID,Detail.AMOUNT,Payment.PAY_AMOUNT
FROM ORDERS Orders
INNER JOIN
(
SELECT ID, SUM(PRICE*COUNT) AMOUNT
FROM ORDER_DETAIL
GROUP BY ID
) Detail
ON Orders.ID=Detail.ID
INNER JOIN
(
SELECT ID, SUM(AMOUNT) PAY_AMOUNT
FROM ORDER_PAYMENT
GROUP BY ID
) Payment
ON Orders.ID=Payment.ID
WHERE PAY_AMOUNT<Detail.AMOUNT
ORDER BY ID
SPL的解决方案：
子表对着订单 ID 分组后，订单 ID 就变成了事实上的主键。之后我们就可以把这些表当作一对一（同维表）关系来处理了。
A |
|
1 |
=T("Orders.txt") |
2 |
=T("OrderDetail.txt") |
3 |
=T("OrderPayment.txt") |
4 |
=A2.groups(ID; sum(PRICE*COUNT):AMOUNT) |
5 |
=A3.groups(ID; sum(AMOUNT):PAY_AMOUNT) |
6 |
=join(A1:Orders,ID; A4:Detail,ID; A5:Payment,ID) |
7 |
=A6.new(Orders.ID, Detail.AMOUNT, Payment.PAY_AMOUNT) |
8 |
=A7.select(PAY_AMOUNT<AMOUNT) |
A1：导入订单表。
A2：导入订单明细表。
A3：导入订单回款表。
A4：分组汇总每个订单的总金额。
A5：分组汇总每个订单的回款总金额。
A6：使用函数 join() 将订单表和汇总后的明细表、回款表按照订单 ID 连接。
A7：创建由字段订单 ID、订单金额和回款金额组成的序表。
A8：从中选出回款金额小于订单金额的记录，即还没有完全付清的订单。
因为 SQL 不提倡分步计算，要把所有过程写到一个句子里，导致这个例子的 SQL 语句有点复杂。要对来讲，SPL 使用分步的逻辑就比较清晰，大体上分为两步：第一步所有子表按照部分主键（主表的主键）进行分组，此时所有子表的事实主键与主表相同了；第二步将多个表按照其主键（或事实主键）进行连接。其实 SQL 和 SPL 的实现原理是一样的，但分步能够让代码更易写。
SQL 与 SPL 对比系列：
SQL 和 SPL 的集合运算对比
SQL 和 SPL 的选出运算对比
SQL 和 SPL 的有序运算对比
SQL 和 SPL 的等值分组对比
SQL 和 SPL 的非等值分组对比
SQL 和 SPL 的有序分组对比
SQL 和 SPL 的一对一和一对多连接对比
SQL 和 SPL 的多对一连接对比
SQL 和 SPL 的多对多连接对比
SQL 和 SPL 的基本静态转置对比
SQL 和 SPL 的复杂静态转置对比
SQL 和 SPL 的动态转置对比
SQL 和 SPL 的递归对比
对润乾产品感兴趣的小伙伴，一定要知道软件还能这样卖哟性价比还不过瘾？ 欢迎加入好多乾计划。
这里可以低价购买软件产品，让已经亲民的价格更加便宜！
这里可以销售产品获取佣金，赚满钱包成为土豪不再是梦！
这里还可以推荐分享抢红包，每次都是好几块钱的巨款哟！
来吧，现在就加入，拿起手机扫码，开始乾包之旅
嗯，还不太了解好多乾？
