介绍SQL Server 2005的CROSS Apply
来源:互联网 发布:绘图软件电脑版 编辑:程序博客网 时间:2024/05/17 01:05
Cross Apply使表可以和表值函数结果进行join, 这样表值函数的参数就可以使用一个结果集,而不是一个标量值,下面是book online的原文,有例子,有解释。
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
As an example, consider the following tables, Employees
and Departments
:
--Create Employees table and insert valuesCREATE TABLE Employees( empid int NOT NULL, mgrid int NULL, empname varchar(25) NOT NULL, salary money NOT NULL, CONSTRAINT PK_Employees PRIMARY KEY(empid),)GOINSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00) INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)GO--Create Departments table and insert valuesCREATE TABLE Departments( deptid INT NOT NULL PRIMARY KEY, deptname VARCHAR(25) NOT NULL, deptmgrid INT NULL REFERENCES Employees)GOINSERT 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)
Most departments in the Departments
table have a manager ID who corresponds to an employee in the Employees
table. The following table-valued function accepts an employee ID as an argument and returns that employee and all of his or her subordinates:
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)ASBEGIN 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 RETURNENDGO
To return all of the subordinates in all levels for the manager of each department, use the following query:
SELECT *FROM Departments AS D CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
Here is the result set.
deptid deptname deptmgrid empid empname mgrid lvl----------- ---------- ----------- ----------- ---------- ----------- ---1 HR 2 2 Andrew 1 01 HR 2 5 Steven 2 11 HR 2 6 Michael 2 12 Marketing 7 7 Robert 3 02 Marketing 7 11 David 7 12 Marketing 7 12 Ron 7 12 Marketing 7 13 Dan 7 12 Marketing 7 14 James 11 23 Finance 8 8 Laura 3 04 R&D 9 9 Ann 3 05 Training 4 4 Margaret 1 05 Training 4 10 Ina 4 1
Notice that each row from the Departments
table is duplicated as many times as there are rows returned from fn_getsubtree for the department's manager.
Also, the Gardening department does not appear in the results. Because this department has no manager, fn_getsubtree returned an empty set for it. By using OUTER APPLY, the Gardening Department will also appear in the result set, with NULL values in the deptmgrid field, as well as in the fields returned by fn_getsubtree.
- 介绍SQL Server 2005的CROSS Apply
- 介绍SQL Server 2005的CROSS Apply
- SQL Server 2005 中 Cross join & Cross Apply & Outer Apply 的区别
- SQL Server 2005 T-SQL cross Apply 与outer apply
- SQL Server CROSS APPLY和OUTER APPLY的应用详解
- SQL Server cross apply 和outer apply
- sql server cross/outer apply 用法
- SQL Server中CROSS APPLY和OUTER APPLY的应用详解
- SQL Server中CROSS APPLY和OUTER APPLY的应用详解
- Sql Server 数据的拆分和合并 cross apply和outer apply
- SQL Server 2005 新增 cross apply 和 outer apply 联接语句
- SQL Server 关于CROSS APPLY 和 OUTER APPLY应用
- SQL:CROSS APPLY和OUTER APPLY的应用
- SQL SERVER 2005 中 Apply的用法
- Sql Server2005对t-sql的增强之Cross Apply
- Sql Server2005对t-sql的增强之Cross Apply
- SQL关于apply的两种形式cross apply和outer apply
- SQL 关于apply的两种形式cross apply 和 outer apply
- 区分大小写
- Ajax 入门总结
- 开会的原则
- ow2-jotm-dist + tomcat5.5+ mysql5.5 配置JDBC JTA事务
- 黑白棋
- 介绍SQL Server 2005的CROSS Apply
- OGRE填空的一个疑问无意中给解决了
- Javascript中的Array对象 Remove方法小技巧
- 浅谈测试web程序的几大要点
- 成功的自动化测试项目实施
- 不用任何破解器,也能破解windows xp 激活 (打击盗版到底,支持正版)
- 内存泄漏检测工具
- SVN1.5服务器安装配置
- 软件测试中性能调优的过程解析