CROSS APPLY的用法

Question

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

Answer 1

SQL的联机帮助，会很清楚的。

Answer 2

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 的不同之处。 下面是完整的测试代码，你可以在 SQL Server 2005 联机帮助上找到： -- create Employees table and insert values
IF OBJECT_ID('Employees') IS NOT NULL
 DROP TABLE Employees
GO
CREATE TABLE Employees
(
 empid INT NOT NULL,
 mgrid INT NULL,
 empname VARCHAR(25) NOT NULL,
 salary MONEY NOT NULL
)
GO
IF OBJECT_ID('Departments') IS NOT NULL
 DROP TABLE Departments
GO
-- create Departments table and insert values
CREATE TABLE Departments
(
 deptid INT NOT NULL PRIMARY KEY,
 deptname VARCHAR(25) NOT NULL,
 deptmgrid INT
)
GO-- fill datas
INSERT  INTO employees VALUES  (1,NULL,'Nancy',00.00)
INSERT  INTO employees VALUES  (2,1,'Andrew',00.00)
INSERT  INTO employees VALUES  (3,1,'Janet',00.00)
INSERT  INTO employees VALUES  (4,1,'Margaret',00.00)
INSERT  INTO employees VALUES  (5,2,'Steven',00.00)
INSERT  INTO employees VALUES  (6,2,'Michael',00.00)
INSERT  INTO employees VALUES  (7,3,'Robert',00.00)
INSERT  INTO employees VALUES  (8,3,'Laura',00.00)
INSERT  INTO employees VALUES  (9,3,'Ann',00.00)
INSERT  INTO employees VALUES  (10,4,'Ina',00.00)
INSERT  INTO employees VALUES  (11,7,'David',00.00)
INSERT  INTO employees VALUES  (12,7,'Ron',00.00)
INSERT  INTO employees VALUES  (13,7,'Dan',00.00)
INSERT  INTO employees VALUES  (14,11,'James',00.00)INSERT  INTO departments VALUES  (1,'HR',2)
INSERT  INTO departments VALUES  (2,'Marketing',7)
INSERT  INTO departments VALUES  (3,'Finance',8)
INSERT  INTO departments VALUES  (4,'R&D',9)
INSERT  INTO departments VALUES  (5,'Training',4)
INSERT  INTO departments VALUES  (6,'Gardening',NULL)
GO
--SELECT * FROM departments-- table-value function
IF OBJECT_ID('fn_getsubtree') IS NOT NULL
 DROP FUNCTION  fn_getsubtree
GO
CREATE  FUNCTION dbo.fn_getsubtree(@empid AS INT) 
RETURNS TABLE 
AS 
RETURN(
  WITH Employees_Subtree(empid, empname, mgrid, lvl)
  AS 
  (
    -- Anchor Member (AM)
    SELECT empid, empname, mgrid, 0
    FROM employees
    WHERE empid = @empid   
    UNION ALL
    -- Recursive Member (RM)
    SELECT e.empid, e.empname, e.mgrid, es.lvl+1
    FROM employees AS e
       join employees_subtree AS es
          ON e.mgrid = es.empid
  )
    SELECT * FROM Employees_Subtree
)
GO-- cross apply query
SELECT  *
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST-- outer apply query
SELECT  *
FROM Departments AS D
    OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/09/10/4537421.aspx

Answer 3

中国风的小例子
create table #T(姓名 varchar(10))
insert into #T values('张三')
insert into #T values('李四')
insert into #T values(NULL )
create table #T2(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into #T2 values('张三' , '语文' , 74)
insert into #T2 values('张三' , '数学' , 83)
insert into #T2 values('张三' , '物理' , 93)
insert into #T2 values(NULL , '数学' , 50)--drop table #t,#T2
goselect 
* 
from 
#T a
cross apply
(select 课程,分数 from #t2 where 姓名=a.姓名) b/*
姓名         课程         分数
---------- ---------- -----------
张三         语文         74
张三         数学         83
张三         物理         93(3 行受影响)*/select 
* 
from 
#T a
outer apply
(select 课程,分数 from #t2 where 姓名=a.姓名) b
/*
姓名         课程         分数
---------- ---------- -----------
张三         语文         74
张三         数学         83
张三         物理         93
李四         NULL       NULL
NULL       NULL       NULL(5 行受影响)
*/

Answer 4

也有别的实现方式吧，而且没怎么试过APPLY的效率

Answer 5

才买了本2008的书《SQLSEVER2008应用案例与解析》，有些浅了。只翻了下，卖了

Answer 6

-- 演示数据
DECLARE @A TABLE(
id int)
INSERT @A
SELECT id = 1 UNION ALL
SELECT id = 2DECLARE @B TABLE(
id int)
INSERT @B
SELECT id = 1 UNION ALL
SELECT id = 3-- 1. 右输入为表时, APPLY操作符与CROSS JOIN的结果一样
SELECT *
FROM @A
CROSS APPLY @B-- 2. 右输入为派生表时, 可以用APPLY操作符模拟JOIN
-- 2.a 模拟 INNER JOIN
SELECT *
FROM @A A
CROSS APPLY(
SELECT * FROM @B
WHERE id = A.id
)B-- 2.b 模拟 LEFT JOIN
SELECT *
FROM @A A
OUTER APPLY(
SELECT * FROM @B
WHERE id = A.id
)B
/*
(2 行受影响)(2 行受影响)
id          id
----------- -----------
1           1
2           1
1           3
2           3(4 行受影响)id          id
----------- -----------
1           1(1 行受影响)id          id
----------- -----------
1           1
2           NULL(2 行受影响)*/

Answer 7

http://d.download.csdn.net/down/1378590/flydr

Answer 8

USE tempdb
GO-- 用于分拆字符串的表值函数
CREATE FUNCTION dbo.f_Split(
@str varchar(max)
)RETURNS @re TABLE(
id int IDENTITY, val varchar(10))
AS
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
GO-- 用于分拆的示例数据
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
GO-- 删除演示数据
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 行受影响)*/

Answer 9

create table #T(姓名 varchar(10))
insert into #T values('张三')
insert into #T values('李四')
  
create table #T2(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into #T2 values('张三' , '语文' , 74)
insert into #T2 values('张三' , '数学' , 83)
insert into #T2 values('张三' , '物理' , 93)
insert into #T2 values('李四' , '语文' , 90)
insert into #T2 values('李四' , '数学' , 66)
insert into #T2 values('李四' , '物理' , 88) -- cross apply的经典用法;求每个人考试科目中成绩的前两名(取出每组中的前几名)
select * from #t t
 cross apply 
(select top 2 * from #t2 where 姓名=t.姓名 order by 分数 desc) as o/*
姓名         姓名         课程         分数
---------- ---------- ---------- -----------
张三         张三         物理         93
张三         张三         数学         83
李四         李四         语文         90
李四         李四         物理         88
*/