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 的实现原理是一样的,但分步能够让代码更易写。