T-SQL中APPLY应用事例解析

      一直都没研究过Apply的用法，最近浏览帖子，是不是会看到，顺便学习一下。

 

    MSDN:使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列表。

   

    没有接入过APPLY的，看到上面的话，肯定云里雾里。

    其实通俗的说，就是APPLY后面是表Table，这个表Table是由表值函数动态生成的。所谓表值函数就是自定义一个函数，返回值是Table。函数的参数为左表（左输入或者APPLY前面查询表）的某一字段。通过对左表的每行调用表值函数，生成临时表，所有的临时表Union组合成一个新表（右输入）。然后左表和右表Join到一起，就是所需的结果集。

    以下示例是获取教师所教授的学生信息：

--创建学生表和教师表 if object_id('Student') is not null drop table Student GO create table Student(sid int ,tid int,sName varchar(10)) insert into Student select 1,1,'zhou' union all select 2,1,'wu' union all select 3,2,'zheng' union all select 4,2,'wang' if object_id('Teacher') is not null drop table Teacher GO create table Teacher(tid int ,tName varchar(10)) insert into Teacher select 1,'li' union all select 2,'zhang' --查询语句 直接用join select * from Teacher t join Student s on t.tid=s.tid --结果集 tid tName sid tid sName ----------- ---------- ----------- ----------- ---------- 1 li 1 1 zhou 1 li 2 1 wu 2 zhang 3 2 zheng 2 zhang 4 2 wang (4 行受影响) --查询语句 用cross apply select * from Teacher t cross apply (select * from Student s where s.tid=t.tid)tmp --查询结果 tid tName sid tid sName ----------- ---------- ----------- ----------- ---------- 1 li 1 1 zhou 1 li 2 1 wu 2 zhang 3 2 zheng 2 zhang 4 2 wang

 

    上面的(select * from Student s where s.tid=t.tid)tmp就是表值函数返回的表，用一下方式写出，大家可能更明白一些。

--创建自定义表值函数 获取此id教师下的学生列表 create function getStudent(@tid int) returns @tmp table(sid int ,tid int,sName varchar(10)) as begin with cte(sid, tid, sName) as ( select sid, tid, sName from Student where tid=@tid ) insert into @tmp select * from cte return end --查询调用 与上面不用表值函数的写法对照一下，会好理解点 select * from Teacher t cross apply getStudent(t.tid) --查询结果 tid tName sid tid sName ----------- ---------- ----------- ----------- ---------- 1 li 1 1 zhou 1 li 2 1 wu 2 zhang 3 2 zheng 2 zhang 4 2 wang (4 行受影响)

    从以上事例可以看书，三种写法的执行结果是相同的。

    但是，这并不意味着apply就等同于join。这只是简单的查询，如果右输入（右表）依赖左表，且经过复杂的运算才可以得到的话，用join就很难实现了，即使实现的话，可读性也会不太好，可能除了自己，别人很难解读。   如果利用apply  + 表值函数的方式就比较明了。具体的可以看MSDN中的事例：

--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), ) GO INSERT 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 ) GO 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) --表值函数 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 --APPLY查询语句 这里需要注意的是Departments表中只有2,7,8,9,4。 --逐次将其代入fn_getsubtree()函数 可以看到结果集与 APPLY查询结果集的右侧一致 SELECT * FROM Departments AS D CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST