SQL Server 2005：Apply运算符

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

APPLY 运算符生成的列的列表是左输入中的列集跟右输入返回的列的列表的组合。类似表间的联接查询

APPLY 有两种形式： CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行（相当于left join)。OUTER APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL(相当于full join)。

准备数据如下：

--Create table Employees table and insert Values

CREATE TABLE Employees

(

    emp_id INT NOT NULL,

    mgr_id INT NULL,

    emp_name VARCHAR(20) NOT NULL,

    emp_salary MONEY NOT NULL,

    CONSTRAINT pk_id PRIMARY KEY(emp_id)

)

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)

CREATE TABLE Departments

(

    dep_id INT NOT NULL,

    dep_name VARCHAR(30) NOT NULL,

    dep_m_id INT NULL REFERENCES Employees(emp_id)

)

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)

-- Departments 表中的大多数部门都有一个经理，该经理ID（即dep_m_id）也是来自与Employees 表中的雇员（即与某一emp_id对应）其中'Gardening'部门没有经理：为null,employees中的各雇员之间存在上下级的关系，mgr_id字段表示了这种关系。

-- 下面的表值函数使用雇员ID 作为参数，并返回该雇员及其所有下属
CREATE FUNCTION fn_getSubTree

(

    @emp_id INT

)

RETURNS @tree TABLE

(

    emp_id INT NOT NULL,

    emp_name VARCHAR(20) NOT NULL,

    mgr_id INT NULL,

    lvl INT NOT NULL

)

AS 

BEGIN

    WITH emp_subtree(emp_id,emp_name,mgr_id,lvl)

    AS

    (

       SELECT emp_id,emp_name,mgr_id,0 FROM Employees WHERE emp_id=@emp_id

       UNION ALL

       SELECT e.emp_id,e.emp_name,e.mgr_id,es.lvl+1

       FROM Employees e JOIN emp_subtree es ON e.mgr_id=es.emp_id

    )

    INSERT INTO @tree 

    SELECT * FROM emp_subtree

    RETURN 

END

select * from fn_getsubtree(3)  ：返回emp_id为3的员工的所有下属

结果如下：

emp_id,emp_name,mgr_id,lvl
3 Janet 1 0
7 Robert 3 1
8 Laura 3 1
9 Ann 3 1
11 David 7 2
12 Ron 7 2
13 Dan 7 2
14 James 11 3
15 James1 14 4

 

 

-- 返回每个部门经理的所有级别的全部下属

SELECT * FROM Departments AS D CROSS APPLY fn_getsubtree(D.dep_m_id) AS ST