Cross Apply的用法

GPS平台、网站建设、软件开发、系统运维，找森大网络科技！
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/?p=2029

透过执行计划可以看出，cross apply类似不带where条件的连接即cross join 。形式上会灵活些.
使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入
。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中
的列集，后跟右输入返回的列的列表。
APPLY 有两种形式： CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返
回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。

SQL Server 2005 新增 cross apply 和 outer apply 联接语句，增加这两个东东有啥作用呢？

我们知道有个 SQL Server 2000 中有个 cross join 是用于交叉联接的。实际上增加 cross apply 和 outer apply 是用于交叉联接表值函数（返回表结果集的函数）的，更重要的是这个函数的参数是另一个表中的字段。这个解释可能有些含混不请，请看下面的例子：

-- 1. cross join 联接两个表

select *

from TABLE_1 as T1

cross join TABLE_2 as T2

-- 2. cross join 联接表和表值函数，表值函数的参数是个“常量”

select *

from TABLE_1 T1

cross join FN_TableValue(100)

-- 3. cross join 联接表和表值函数，表值函数的参数是“表T1中的字段”

select *

from TABLE_1 T1

cross join FN_TableValue(T1.column_a)

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "T1.column_a" could not be bound.

最后的这个查询的语法有错误。在 cross join 时，表值函数的参数不能是表 T1 的字段，为啥不能这样做呢？我猜可能微软当时没有加这个功能：），后来有客户抱怨后，于是微软就增加了 cross apply 和 outer apply 来完善，请看 cross apply, outer apply 的例子：

-- 4. cross apply

select *

from TABLE_1 T1

cross apply FN_TableValue(T1.column_a)

-- 5. outer apply

select *

from TABLE_1 T1

outer apply FN_TableValue(T1.column_a)

cross apply 和 outer apply 对于 T1 中的每一行都和派生表（表值函数根据T1当前行数据生成的动态结果集）做了一个交叉联接。cross apply 和 outer apply 的区别在于：如果根据 T1 的某行数据生成的派生表为空，cross apply 后的结果集就不包含 T1 中的这行数据，而 outer apply 仍会包含这行数据，并且派生表的所有字段值都为 NULL。

下面的例子摘自微软 SQL Server 2005 联机帮助，它很清楚的展现了 cross apply 和 outer apply 的不同之处：

-- cross apply

select *

from Departments as D

cross apply fn_getsubtree(D.deptmgrid) as ST

deptid deptname deptmgrid empid empname mgrid lvl

----------- ----------- ----------- ----------- ----------- ----------- ------

1 HR 2 2 Andrew 1 0

1 HR 2 5 Steven 2 1

1 HR 2 6 Michael 2 1

2 Marketing 7 7 Robert 3 0

2 Marketing 7 11 David 7 1

2 Marketing 7 12 Ron 7 1

2 Marketing 7 13 Dan 7 1

2 Marketing 7 14 James 11 2

3 Finance 8 8 Laura 3 0

4 R&D 9 9 Ann 3 0

5 Training 4 4 Margaret 1 0

5 Training 4 10 Ina 4 1

(12 row(s) affected)

-- outer apply

select *

from Departments as D

outer apply fn_getsubtree(D.deptmgrid) as ST

deptid deptname deptmgrid empid empname mgrid lvl

----------- ----------- ----------- ----------- ----------- ----------- ------

1 HR 2 2 Andrew 1 0

1 HR 2 5 Steven 2 1

1 HR 2 6 Michael 2 1

2 Marketing 7 7 Robert 3 0

2 Marketing 7 11 David 7 1

2 Marketing 7 12 Ron 7 1

2 Marketing 7 13 Dan 7 1

2 Marketing 7 14 James 11 2

3 Finance 8 8 Laura 3 0

4 R&D 9 9 Ann 3 0

5 Training 4 4 Margaret 1 0

5 Training 4 10 Ina 4 1

6 Gardening NULL NULL NULL NULL NULL

(13 row(s) affected)

注意 outer apply 结果集中多出的最后一行。当 Departments 的最后一行在进行交叉联接时：deptmgrid 为 NULL，fn_getsubtree(D.deptmgrid) 生成的派生表中没有数据，但 outer apply 仍会包含这一行数据，这就是它和 cross join 的不同之处。

小例子一

cross apply的经典用法;求每个人考试科目中成绩的前两名(取出每组中的前几名)

疑问

USE tempdb

-- 用于分拆字符串的表值函数

CREATE FUNCTION dbo.f_Split(

@str varchar(max)

)RETURNS @re TABLE(

id int IDENTITY, val varchar(10))

BEGIN

DECLARE @pos int

SET @pos = CHARINDEX(',', @str)

WHILE @pos > 0

BEGIN

INSERT @re(val) VALUES(LEFT(@str, @pos - 1))

SELECT

@str = STUFF(@str, 1, @pos, ''),

@pos = CHARINDEX(',', @str)

END

IF @str > ''

INSERT @re(val) VALUES(@str)

RETURN

END

-- 用于分拆的示例数据

DECLARE @tb TABLE(

col varchar(max))

INSERT @tb

SELECT col = '1,2,3' UNION ALL

SELECT col = NULL UNION ALL

SELECT col = '' UNION ALL

SELECT col = '1'

-- 使用 CROSS APPLY

SELECT *

FROM @tb A

CROSS APPLY dbo.f_Split(A.col) B

-- 使用 OUTER APPLY

SELECT *

FROM @tb A

OUTER APPLY dbo.f_Split(A.col) B

-- 删除演示数据

DROP FUNCTION dbo.f_Split

(4 行受影响)

col id val

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------

1,2,3 1 1

1,2,3 2 2

1,2,3 3 3

1 1 1

(4 行受影响)

col id val

1,2,3 1 1

1,2,3 2 2

1,2,3 3 3

NULL NULL NULL

NULL NULL

1 1 1

(6 行受影响)

GPS平台、网站建设、软件开发、系统运维，找森大网络科技！
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/?p=2029