[]
        
(Showing Draft Content)

表连接函数Cross/Outer Apply

Cross Apply 和 Outer Apply 均是表连接函数,可以用来连接两个表类型的表达式。常与表值函数(UnwindJson和 UnwindArray)一起使用。

Cross Apply

Cross Apply 与Join 类似,也是对两个表进行操作,子查询语句可以做为内部表。

Cross Apply 等效于隐式连接条件为1 = 1的 INNER JOIN(或更精确地说,它类似于具有相关子查询的 CROSS JOIN)。

我们来看这个SQL语句

select Table1.Name, tmp.Value as Tag from Table1 cross apply UnwindArray(SPLIT(Table1.Tags,',')) tmp

Table1.Tags是对Table1的列进行引用。

下图中可查看 CROSS APPLY 的工作过程(点击可查看大图):


image2020-6-2_18-5-15.png

如果表值函数引用列,则必须将其与CROSS APPLY 或 OUTER APPLY一起使用 ,否则将无法正确解析数据。

Outer Apply

Outer ApplyCross Apply 略有不同。

当与一个表值函数进行 Outer Apply 连接且表值函数返回空行集合时,实际上外部数据行在进行合并时,所有的单元格都是DBNull。所以合并后,都返回了DBNUll。

将上例中的Cross Apply 换成 Outer Apply 后,工作原理如下(点击可查看大图):

image2020-6-3_10-14-14.png

可见,Outer Apply 与Join 类似,也是对两个表进行操作,子查询语句可以做为内部表。

Outer Apply 等效于隐式连接条件为1 = 1的 LEFT OUTER JOIN。

UnwindJson+Apply 示例

UnwindJson 有时会返回 JSON 文本作为单元格值。您可能很好奇,那我们如何使用这些 JSON 文本?答案是使用多个 UnwindJson 和 APPLY。

以下是一个包含公司部门,组和开发人员信息的 json 示例:

{
    "Company": {
        "Name": "GrapeCity",
        "Addr": "Xi'an",
        "Phone": "029-88331988",
        "Departments": [
            {
                "Id": "DD1",
                "Name": "Develop Department 1",
                "Groups": [
                    {
                        "Id": "DD1-GRP1",
                        "Name": "DD1 Group1",
                        "Developers": [
                            {
                                "Role": "PM",
                                "Name": "AAA"
                            },
                            {
                                "Role": "DL",
                                "Name": "BBB"
                            },
                            {
                                "Role": "Dev",
                                "Name": "CCC"
                            }
                        ]
                    },
                    {
                        "Id": "DD1-GRP2",
                        "Name": "DD1 Group2",
                        "Developers": [
                            {
                                "Role": "Dev",
                                "Name": "DDD"
                            },
                            {
                                "Role": "Tester",
                                "Name": "EEE"
                            }
                        ]
                    }
                ]
            },
            {
                "Id": "DD2",
                "Name": "Develop Department 2",
                "Groups": [
                    {
                        "Id": "DD2-GRP1",
                        "Name": "DD2 Group1",
                        "Developers": [
                            {
                                "Role": "PM",
                                "Name": "FFF"
                            },
                            {
                                "Role": "Dev",
                                "Name": "GGG"
                            }
                        ]
                    },
                    {
                        "Id": "DD2-GRP2",
                        "Name": "DD2 Group2",
                        "Developers": [
                            {
                                "Role": "Dev",
                                "Name": "HHH"
                            },
                            {
                                "Role": "Dev",
                                "Name": "III"
                            },
                            {
                                "Role": "Tester",
                                "Name": "JJJ"
                            }
                        ]
                    }
                ]
            },
            {
                "Id": "DD3",
                "Name": "",
                "Groups": [
                    {
                        "Id": "DD3-GRP1",
                        "Name": "DD3 Group1",
                        "Developers": [
                            {
                                "Role": "DL",
                                "Name": "KKK"
                            },
                            {
                                "Role": "Dev",
                                "Name": "LLL"
                            },
                            {
                                "Role": "Tester",
                                "Name": "MMM"
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

现在,我们使用SQL 语句展开其所有嵌套的数组并生成概览数据表。

select
    departmentDetails.Id as DepId,
    groupDetails.Id as GroupId,
    developers.Name as DevName,
    developers.Role as DevRole
from
    UnwindJson(@source, '$.Company.Departments[*]') departments
outer apply
    UnwindJson(departments.Value) departmentDetails with ([Id] varchar, [Groups] varchar AS JSON)
outer apply
    UnwindJson(departmentDetails.Groups, '$[*]') groupDetails with ([Id] varchar, [Developers] varchar AS JSON)
outer apply
    UnwindJson(groupDetails.Developers, '$[*]') developers with ([Name] varchar, [Role] varchar)

展开后,结果如下:

DepId

GroupId

DevName

DevRole

DD1

DD1-GRP1

AAA

PM

DD1

DD1-GRP1

BBB

DL

DD1

DD1-GRP1

CCC

Dev

DD1

DD1-GRP2

DDD

Dev

DD1

DD1-GRP2

EEE

Tester

DD2

DD2-GRP1

FFF

PM

DD2

DD2-GRP1

GGG

Dev

DD2

DD2-GRP2

HHH

Dev

DD2

DD2-GRP2

III

Dev

DD2

DD2-GRP2

JJJ

Tester

DD3

DD3-GRP1

KKK

DL

DD3

DD3-GRP1

LLL

Dev

DD2

DD3-GRP1

MMM

Tester