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
create table Employees
(
empid int not null,
mgrid int NULL,
empname varchar(25) not null,
salary money not null
)
go
-- create Departments table and insert values
create table Departments
(
deptid int not null primary key,
deptname varchar(25) not null
)
go
-- fill datas
insert into employees values(1 , NULL, 'Nancy' , 000.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
-- table-value function
create function dbo.fn_getsubtree(@empid AS INT) returns @TREE table
(
empid int not null,
empname varchar(25) not null,
mgrid int null,
lvl int not null
)
as
begin
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
)
insert into @TREE
select * from Employees_Subtree
return
end
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
分享到:
相关推荐
SQL Server数据库操作中,在2005以上的版本新增加了一个APPLY表运算符的功能
[Microsoft SQL Server 2005 T-SQL &.NET高级编程(第3版)]一书源程序代码
Microsoft SQL Server Management Studio Express(SSMSE)是一种免费、易于使用的图形管理工具,用于管理SQL Server 2005 Express Edition和SQL Server 2005 ExpressEdition with Advanced Services。注意:SSMSE ...
下面把sqlserver中cross apply和outer apply关键字具体介绍展示如下: 1.CROSS APPLY 和OUTER APPLY MSDN解释如下(个人理解不是很清晰): 使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值...
Microsoft SQL Server 2005 向后兼容组件SQLServer2005_BC.msi
Microsoft SQL Server 2005 Backward Compatibility Components (Microsoft SQL Server 2005 向后兼容组件) SQL Server Backward Compatibility 包中包含 最新版本的 Data Transformation Services 2000 运行时 ...
Sql Server 2008 native client 32&64; 数据库客户端连接
JAVA 连接sqlserver类 已封装好 & sqljdbc.jar 包含sqljdbc_4.jar 封装完整Sqlserver类
SQLServer2005_BC 32/64位 向后兼容兼容补丁
数据库连接客户端SQL Server 2008 Native Client 32&64
sql server 2005 向后兼容程序32位版 用于新版本程序兼容旧版本数据库
资源名称:SQL Server 2005数据库开发实战内容简介:本书综合讲解了Microsoft SQL Server 2005程序开发与界面设计的相关知识,内容全面,结构合理,论述清晰,对Microsoft SQL Server 2005技术及其实际应用都有独到...
SQL Server2005基本方法和语句
SQL Server 2005 启用远程连接
本书通过大量翔实的示例,向读者展示了SQL Server 2005数据库的原理、开发、应用及管理的相关技术和技巧。全书15章,可分为3大部分:第1部分(第1、2章)概述了数据库的基本原理,包括数据处理的基础知识、数据库的基本...
sql server 2005 sp4补丁。 sql server 2005 sp4补丁 sql server 2005 sp4补丁 希望有人能用到。
SQL Server 2005 杂谈 在SQL Server2005中按列 连接字符串 三种方法 doc SQL Server 2005 杂谈 在SQL Server2005中按列 连接字符串 三种方法 doc
完全卸载sql server 2005比较麻烦,这是关于卸载sql server 2005的一个方法。