代码改变世界

T-SQL中APPLY应用事例解析(转载)

2013-05-02 10:03  Sam Jin  阅读(175)  评论(0)    收藏  举报

一直都没研究过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