使用 APPLY
APPLY 运算符的左操作数和右操作数都是表表达式。这些操作数之间的主要区别是,right_table_source 可以使用表值函数,从 left_table_source 获取一个列作为函数的参数之一。left_table_source 可以包括表值函数,但不能以来自 right_table_source 的列作为参数。
APPLY 运算符通过以下方式工作,以便为 FROM 子句生成表源:
- 对 left_table_source 的每行计算 right_table_source 的值来生成行集。
right_table_source 中的值依赖于 left_table_source。right_table_source 可以按以下方式近似表示:TVF(left_table_source.row)
,其中,TVF
是表值函数。
- 通过执行 UNION ALL 操作,将计算 right_table_source 的值时为每行生成的结果集与 left_table_source 组合起来。
APPLY 运算符的结果生成的列的列表是来自 left_table_source(与来自 right_table_source 的列的列表相组合)的一组列。
演示一下APPLY 运算符的用法:
--建一个表
--插入测试数据
INSERT INTO MyData VALUES('') INSERT INTO MyData VALUES('a,b,c') INSERT INTO MyData VALUES('q') INSERT INTO MyData VALUES('i,p') GO select * from MyData go |
--查询结果
ids Data 1 2 a,b,c 3 q 4 i,p |
建立一个表,作用是:按逗号分解字符,分解出的每一个字符做一行数据返回
create FUNCTION fun_MyData( @data AS NVARCHAR(1000) ) RETURNS @tem TABLE( id INT , value nvarchar(100) ) AS BEGIN select @data=isnull(@data,'') if len(@data)=0 return --字符长度为0 ,退出 declare @id AS INT select @id=1 declare @end AS INT select @end = CHARINDEX(',', @data) while(@end>0) begin insert into @tem(id,value) select @id,left(@data,@end-1) select @id=@id+1 select @data=right(@data,len(@data)-@end) select @end = CHARINDEX(',', @data) end if len(@data)>0 begin insert into @tem(id,value) select @id,@data end RETURN END |
SELECT m.ids, f.*
FROM MyData m CROSS APPLY fun_MyData(data) f
go
--结果
ids id value
2 1 a
2 2 b
2 3 c
3 1 q
4 1 i
4 2 p
SELECT m.ids, f.* FROM MyData m OUTER APPLY fun_MyData(data) f g o |
--结果
ids id value
1 NULL NULL
2 1 a
2 2 b
2 3 c
3 1 q
4 1 i
4 2 p
我们看到OUTER APPLY返回的结果行比CROSS APPLY多。
这一点有点象inner join(内部联接)和Left Outer Join(左外部联接)之间的关系.
其实APPLY有两种形式:CROSS APPLY 和 OUTER APPLY。
CROSS APPLY仅返回外部表中通过表值函数生成结果集的行。
OUTER APPLY既返回生成结果集的行,也返回不生成结果集的行,其中表值函数生成的列中的值为 NULL。