"【摘要】连接（JOIN）用于把来自两个或多个表的记录结合起来。SQL 和 SPL 是大家比较熟悉的程序语言，本文将探讨对于连接问题，这两种语言 .."

WuNan 北京
乾学院 42 号会员
1 回帖 • 2,706 浏览 • 3 年前

SQL 和 SPL 的一对一和一对多连接对比

计算＆AI

SQL(24) SPL(62) 集算器(488) 连接(10) JOIN(16) 一对一(4) 一对多(4) 多对一(4) 多对多(4) 等值连接(2)

【摘要】
连接（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(ORDER; sum(AMOUNT):PAY_AMOUNT)
6	=join(A1:Orders,ID; A4:Detail,ID; A5:Payment,ORDER)
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 的实现原理是一样的，但分步能够让代码更易写。