CREATE TABLE [dbo].[Customers](    [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NOT NULL,    [city] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,PRIMARY KEY CLUSTERED (    [customerid] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]insert into dbo.Customers values(‘FISSA‘,‘Madrid‘);insert into dbo.Customers values(‘FRNDO‘,‘Madrid‘);insert into dbo.Customers values(‘KRLOS‘,‘Madrid‘);insert into dbo.Customers values(‘MRPHS‘,‘Zion‘);select * from dbo.CustomersCREATE TABLE [dbo].[Orders](    [orderid] [int] NOT NULL,    [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NULL,PRIMARY KEY CLUSTERED (    [orderid] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]insert into dbo.Orders values(1,‘FRNDO‘);insert into dbo.Orders values(2,‘FRNDO‘);insert into dbo.Orders values(3,‘KRLOS‘);insert into dbo.Orders values(4,‘KRLOS‘);insert into dbo.Orders values(5,‘KRLOS‘);insert into dbo.Orders values(6,‘MRPHS‘);insert into dbo.Orders values(7,null);select * from dbo.orders--得到每个消费者最新的两个订单：--用cross applyselect *from dbo.Customers as C cross apply    (select top 2 *     from dbo.Orders as O     where C.customerid=O.customerid     order by orderid desc) as CA--过程分析：--它是先得出左表【dbo.Customers】里的数据，然后把此数据一条一条的放入右表表式中，分别得出结果集，最后把结果集整合到一起就是最终的返回结果集了--（T1的数据 像for循环一样 一条一条的进入到T2中 然后返回一个集合  最后把所有的集合整合到一块  就是最终的结果），--最后我们再理解一下上面让记着的话（使用apply就像是先计算左输入，让后为左输入中的每一行计算一次右输入）是不是有所明白了。--实验：用outer apply 试试看看的到的结果：select *from dbo.Customers as C outer apply    (select top 2 *     from dbo.Orders as O     where C.customerid=O.customerid     order by orderid desc) as CA--结果分析：--发现outer apply得到的结果比cross多了一行，我们结合上面所写的区别（cross apply和outer apply 总是包含步骤A1，只有outer apply包含步骤A2，--如果cross apply左行应用右表表达式时返回空积，则不返回该行。而outer apply返回改行，并且改行的右表表达式的属性为null）就会知道了。

--下面是完整的测试代码，你可以在 SQL Server 2005 联机帮助上找到： -- create Employees table and insert valuesIF OBJECT_ID(‘Employees‘) IS NOT NULL DROP TABLE EmployeesGOCREATE TABLE Employees( empid INT NOT NULL, mgrid INT NULL, empname VARCHAR(25) NOT NULL, salary MONEY NOT NULL)GOIF OBJECT_ID(‘Departments‘) IS NOT NULL DROP TABLE DepartmentsGO-- create Departments table and insert valuesCREATE TABLE Departments( deptid INT NOT NULL PRIMARY KEY, deptname VARCHAR(25) NOT NULL, deptmgrid INT)GO-- fill datasINSERT  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 functionIF OBJECT_ID(‘fn_getsubtree‘) IS NOT NULL DROP FUNCTION  fn_getsubtreeGOCREATE  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 querySELECT  *FROM Departments AS D    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST-- outer apply querySELECT  *FROM Departments AS D    OUTER APPLY fn_getsubtree(D.deptmgrid) AS STselect * from employeesselect * from Departmentsselect * from fn_getsubtree(2)select * from fn_getsubtree(3)select * from fn_getsubtree(4)select * from fn_getsubtree(5)select * from fn_getsubtree(6)

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,#T2goselect     * from     #T across apply    (select 课程,分数 from #t2 where 姓名=a.姓名) b/*姓名         课程         分数---------- ---------- -----------张三         语文         74张三         数学         83张三         物理         93(3 行受影响)*/select     * from     #T aouter apply    (select 课程,分数 from #t2 where 姓名=a.姓名) b/*姓名         课程         分数---------- ---------- -----------张三         语文         74张三         数学         83张三         物理         93李四         NULL       NULLNULL       NULL       NULL(5 行受影响)*/