[]
Cross Apply 和 Outer Apply 均是表连接函数,可以用来连接两个表类型的表达式。常与表值函数(UnwindJson和 UnwindArray)一起使用。
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 的工作过程(点击可查看大图):
如果表值函数引用列,则必须将其与CROSS APPLY 或 OUTER APPLY一起使用 ,否则将无法正确解析数据。
Outer Apply 与 Cross Apply 略有不同。
当与一个表值函数进行 Outer Apply 连接且表值函数返回空行集合时,实际上外部数据行在进行合并时,所有的单元格都是DBNull。所以合并后,都返回了DBNUll。
将上例中的Cross Apply 换成 Outer Apply 后,工作原理如下(点击可查看大图):
可见,Outer Apply 与Join 类似,也是对两个表进行操作,子查询语句可以做为内部表。
Outer Apply 等效于隐式连接条件为1 = 1的 LEFT OUTER JOIN。
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 |