SQL - Apply运算符用法.doc

使用 APPLYAPPLY 运算符的左操作数和右操作数都是表表达式。这些操作数之间的主要区别是，right_table_source 可以使用表值函数，从 left_table_source 获取一个列作为函数的参数之一。left_table_source 可以包括表值函数，但不能以来自 right_table_source 的列作为参数。APPLY 运算符通过以下方式工作，以便为 FROM 子句生成表源：1. 对 left_table_source 的每行计算 right_table_source 的值来生成行集。right_table_source 中的值依赖于 left_table_source。right_table_source 可以按以下方式近似表示：TVF(left_table_source.row)，其中，TVF 是表值函数。2. 通过执行 UNION ALL 操作，将计算 right_table_source 的值时为每行生成的结果集与 left_table_source 组合起来。APPLY 运算符的结果生成的列的列表是来自 left_table_source（与来自 right_table_source 的列的列表相组合）的一组列。SQL Server 2008 联机丛书 使用 APPLY访问和更改数据库数据 查询基础知识 使用 FROM 子句 使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列表。注意：若要使用 APPLY，数据库兼容级别必须为 90。 APPLY 有两种形式：CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。例如，考虑下列表 Employees 和 Departments：-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 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 values.CREATE 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);Departments 表中的多数部门都具有一个经理 ID，这些 ID 与 Employees 表中的雇员相对应。以下表值函数接受雇员 ID 作为参数，并返回该雇员和他/她的所有下属。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若要返回每个部门经理的各级下属，请使用以下查询。SELECT D.deptid, D.deptname, D.deptmgrid ,ST.empid, ST.empname, ST.mgridFROM Departments AS D CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;下面是结果集： 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注意，Departments 表中每一行复制的次数与针对部门经理执行 fn_getsubt