SQL 和 SPL 的多对一连接对比

【摘要】
    连接(JOIN)用于把来自两个或多个表的记录结合起来。SQL 和 SPL 是大家比较熟悉的程序语言,本文将探讨对于连接问题,这两种语言的解决方案和基本原理。如何简便快捷的处理连接运算,这里为你全程解析,并提供 SQL 和 SPL 示例代码。SQL 和 SPL 的多对一连接对比

前文介绍了一对一、多对一两种表间关联关系,本文继续介绍多对一关联关系。

多对一关联,是指一张表的任意条记录能与另外一张表的一条记录进行对应。多对一关联关系,常见于有外键关系的两个表。表 A 的某些字段与表 B 的主键关联。A 表中与 B 表主键关联的字段称为指向 B 表的外键,B 也称为 A 的外键表

例如有员工表和部门表,其中员工表的部门 ID 字段,指向了部门表的 ID 字段。每个员工都有一个部门,但是每个部门可以有多个员工,员工表和部门表是多对一的关系。

我们经常把一对多和多对一混在一起来讲,其实是受了 SQL 的误导。在 SQL 中对这两种关联关系并没有进行区分,也没有单独提供各自的解决方案。

解决多对一关联的思路与一对多完全不同,我们可以在连接时将外键值转换成对应记录,或者将需要的字段值附加到“多”表上,然后就可以当作是一张表进行查询运算了。但是在 SQL 中并没有单独支持多对一关联关系,仍然是使用 JOIN 进行连接。而 SPL 则是提供了 A.switch() 等函数,针对外键表连接(多对一)进行优化和提速。

 

【例 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

 

SQL的解决方案:

    SQL处理多对一关系时,还是使用 JOIN 语句将两表连接,连接条件是外键字段与外键表的主键字段相等。这里要查询所有员工,所以使用了左连接 LEFT JOIN,SQL 语句如下:

   

    SELECT

      E.NAME, D.NAME DEPT_NAME

   FROM EMPLOYEE E

   LEFT JOIN

   DEPARTMENT D

   ON E.DEPARTMENTID=D.ID

 

SPL的解决方案:

    SPL提供了函数 A.switch(),将编码字段的键值转换为对应的记录。


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 字段获得。

 

    由于这个例子比较简单,SQL 和 SPL 都可以很好的解决。可能有的同学会有疑问,在连接时将需要的外键字段引入就可以了,为什么还要进行外键对象化呢?接下来我们用一个稍微复杂一点的例子来说明。

 

【例 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

 

SQL的解决方案:

    在本例中,我们不能简单的将需要的外键表字段添加到员工表上,需要进行两次连接:一是部门表与员工表通过员工 ID 进行连接, 查找经理的国籍。二是员工表与部门表通过部门 ID 进行连接,查找员工的部门。SQL 语句如下:

 

   SELECT *

   FROM EMPLOYEE E2

   LEFT JOIN

      (SELECT D1.ID,D1.MANAGER,E1.NATION MANAGER_NATION

      FROM DEPARTMENT D1

      LEFT JOIN EMPLOYEE E1

      ON D1.MANAGER=E1.ID

   ) D2

   ON E2.DEPARTMENTID=D2.ID

   WHERE D2.MANAGER_NATION='Chinese' AND E2.NATION='American'

   

    这个 SQL 看起来就比较复杂了。因为每增加一次连接 SQL 语句就要多嵌套一层,并且要提前想清楚我们需要外键表中的哪些字段。

 

SPL的解决方案:

    SPL还是可以通过函数 A.switch() 进行外键的对象化来解决问题:


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:选出经理是中国籍的美国籍员工。

 

与 SQL 语句相比,SPL 语句并没有变得复杂。多了一层关联关系,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

 

SQL的解决方案:

    我们想要在连接时,删除不匹配的记录(选出课程不是 Matlab 的),可以使用内连接。SQL 语句如下:

 

   SELECT

      COUNT(*) COUNT

   FROM SELECT_COURSE SC

   INNER JOIN

      COURSE C

   ON SC.COURSEID=C.ID

   WHERE NAME='Matlab'

 

SPL的解决方案:

    在函数 A.switch() 中,选项 @i 用于在连接时删除不匹配的记录。


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

 

SQL的解决方案:

    我们想要在连接时,只保留不匹配的记录(销售表的客户 ID 不在客户表中的),在 SQL 中我们可以使用 NOT IN 或者 NOT EXISTS 来实现。SQL 语句如下:

 

   SELECT *

   FROM SALES S

   WHERE

      EXTRACT (YEAR FROM ORDERDATE)=2014

      AND

      CUSTOMERID NOT IN

         (SELECT DISTINCT ID

         FROM CUSTOMER)

 

    或者:

 

   SELECT *

   FROM SALES S

   WHERE

      EXTRACT (YEAR FROM ORDERDATE)=2014

      AND

      NOT EXISTS

         (SELECT *

         FROM CUSTOMER C

         WHERE S.CUSTOMERID=C.ID)

 

SPL的解决方案:

    在函数 A.switch() 中,选项 @d 时只保留不匹配的记录。


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

 

SQL的解决方案:

    在组织机构表中,上级机构 ID(PARENT_ID)指向的还是本表,这是一个自连接的例子。我们可以对组织机构表查询两次,分别看作两个表,再使用之前用到的连接方式即可。SQL 语句如下:

 

   SELECT

      ORG1.ID,ORG1.ORG_NAME,ORG2.ORG_NAME PARENT_NAME

   FROM

      ORGANIZATION ORG1

   LEFT JOIN

   ORGANIZATION ORG2

   ON ORG1.PARENT_ID=ORG2.ID

   ORDER BY ID

 

SPL的解决方案:

    在 SPL 中,我们还是可以使用函数 A.switch() 进行外键对象化,外键表选择自身即可。


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】 查询 2015 年每个客户的名称和订单总金额。订单表、订单明细表和客户表部分数据如下:

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

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

 

SQL的解决方案:

    本题中同时存在一对多(订单表和订单明细表)和多对一(订单表和客户表)关联关系。在 SQL 中对于一对多和多对一关系是没有特意区分的,但是我们必须搞清楚两者的不同。我们可以先处理外键表(多对一),将相应外键记录或需要的字段值附加到“多”表上,然后就变成只有主子表(一对多)的情况了。SQL 语句如下:

 

   SELECT

      CUSTOMER_NAME, SUM(AMOUNT) AMOUNT

   FROM (

      SELECT

         Orders1.ID, CUSTOMER_NAME, Detail.PRICE*Detail.COUNT AMOUNT

      FROM (

         SELECT

            Orders.ID,Customer.NAME CUSTOMER_NAME

         FROM ORDERS Orders

         LEFT JOIN

         CUSTOMER Customer

         ON Orders.CUSTOMERID=Customer.ID

         WHERE EXTRACT (YEAR FROM ORDER_DATE)=2015

      ) Orders1

      INNER JOIN

      ORDER_DETAIL Detail

      ON Orders1.ID=Detail.ID

   )

   GROUP BY CUSTOMER_NAME

   ORDER BY CUSTOMER_NAME

 

SPL的解决方案:

    在 SPL 中,我们首先使用外键对象化,将订单表中的客户 ID 转换为对应的客户记录然后就只有单个主子表关系了,使用函数 join() 进行连接即可。


A

1

=T("Orders.txt")

2

=T("Customer.txt")

3

=A1.select(year(ORDER_DATE)==2015).switch(CUSTOMERID, A2:ID)

4

=T("OrderDetail.txt").group(ID)

5

=join(A3:Orders, ID;A4:Detail, ID)

6

=A5.groups(Orders.CUSTOMERID.NAME; Detail.sum(PRICE*COUNT):AMOUNT)

A1:导入订单表。

A2:导入客户表。

A3:选出 2015 年的订单,并使用函数 A.switch() 将客户 ID 外键对象化,转换为对应的客户记录。

A4:导入订单明细表,并按订单 ID 分组。

A5:使用函数 join() 按照订单 ID 连接订单表和订单明细表。

A6:分组汇总每个客户的总销售额。

 

【例 7】 查询商品名称中包含“water”,在 2014 年下单,订单总金额大于 200 元,没有使用分期,得到 5 星好评的订单信息(订单号、产品名称和总金额)。表间关联关系和部分数据如下:

..

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

EVALUATION:

ID

SCORE

DATE

COMMENT

10248

4

2012/07/12


10249

1

2012/07/06


10250

4

2012/07/10


10251

2

2012/07/11


10252

3

2012/07/16


PRODUCT:

ID

NAME

SUPPLIERID

CATEGORY

1

Apple   Juice

2

1

2

Milk

1

1

3

Tomato   sauce

1

2

4

Salt

2

2

5

Sesame   oil

2

2

 

SQL的解决方案:

    在本题中,既有一对多和多对一关系,又有一对一关系。如果我们不区分这几种连接方式,直接用 JOIN 来连接,就会产生多对多的关系,这样做是错误的。我们应该先处理多对一关系(外键表),将相应外键记录或需要的字段值附加到“多”表上,然后就变成只有一对一和一对多关系的情况了。再将子表按照主表主键(订单 ID)进行分组,主表主键(订单 ID)就变成子表事实上的主键了。最后将订单表、订单明细表、订单回款表和评价表按照订单 ID 连接即可。SQL 语句如下:

 

   SELECT

      Orders.ID,Detail1.NAME, Detail1.AMOUNT

   FROM (

      SELECT ID

      FROM ORDERS

      WHERE

         EXTRACT (YEAR FROM Orders.ORDER_DATE)=2014

   ) Orders

   INNER JOIN (

      SELECT ID,NAME, SUM(AMOUNT) AMOUNT

      FROM (

         SELECT

            Detail.ID,Product.NAME,Detail.PRICE*Detail.COUNT AMOUNT

         FROM ORDER_DETAIL Detail

         INNER JOIN

         PRODUCT Product

         ON Detail.PRODUCTID=Product.ID

         WHERE NAME LIKE '%water%'

      )

      GROUP BY ID,NAME

   ) Detail1

   ON Orders.ID=Detail1.ID

   INNER JOIN(

      SELECT

         DISTINCT ID

      FROM ORDER_PAYMENT

      WHERE INSTALMENTS=0

   ) Payment

   ON Orders.ID = Payment.ID

   INNER JOIN(

      SELECT ID

      FROM EVALUATION

      WHERE SCORE=5

   ) Evaluation

   ON Orders.ID = Evaluation.ID

 

这个 SQL 已经很难看懂了,无论是编写 SQL,还是后期维护,都需要不少工作量。更重要的是,由于需要多次连接和嵌套查询,我们很难判断 SQL 语句的正确性。

 

SPL的解决方案:


A

1

=T("Orders.txt").select(year(ORDER_DATE)==2014)

2

=T("Product.txt").select(like(NAME,   "*water*"))

3

=T("OrderDetail.txt").switch@i(PRODUCTID, A2:ID)

4

=A3.group(ID).select(sum(PRICE*COUNT)>200)

5

=T("OrderPayment.txt").select(INSTALMENTS==0).group(ID)

6

=T("Evaluation.txt").select(SCORE==5)

7

=join(A1:Orders,ID;A4:Detail,ID;A5:Payment,ID;A6:Evaluation,ID)

8

=A7.new(Orders.ID,Detail.PRODUCTID.NAME,Detail.sum(PRICE*COUNT):AMOUNT)

A1:导入订单表,并选出 2014 年的记录。

A2:导入产品表,并选出名称含有 water 的产品。

A3:导入订单明细表,并将产品 ID 字段外键对象化,转换为相应的产品记录。

A4:将订单明细按照订单 ID 分组,并选出总金额大于 200 的记录。

A5:导入订单回款表,并选出没有使用分期付款的记录。

A6:导入评价表,并选出 5 星好评的记录。

A7:使用函数 join() 按照订单 ID 连接订单表、订单明细表、订单回款表和评价表。

A8:返回满足条件的订单 ID、产品名称和订单总金额。

 

    与 SQL 相比,SPL 脚本只是比前面的例子多了两行代码。而且每个表格的导入、选出、分组等运算,都是各自独立的,连接的代码只有A7一行。SPL 脚本的逻辑非常清晰,就是按照前面介绍的思路一步一步书写就完成了。

 

    通过以上的例子我们可以看到,多对一与一对多关系是有很大的区别的。SQL 没有区分两者的不同,需要用户自己去注意两者的区别。而在 SPL 中,明确的区分了两者的不同,分别提供了不同的函数来支持多对一和一对多两种关联关系。我们再来按照 SPL 的解题方式,总结一下多表关联查询的思路:

(1)   有多对一关联关系(外键表)时,将相应外键记录或需要的字段值附加到“多”表上。

(2)   有一对多关联关系(主子表)时,子表按照主表主键分组后,主表主键就成为了子表事实上的主键。

(3)   多表按照主键(或事实上的主键)进行连接。


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 的递归对比

以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾