SQL Server 2005 T-SQL cross Apply 与outer apply

SQL Server 2005 T-SQL Apply

透过执行计划可以看出，cross apply类似不带where条件的连接即cross join （交叉连接即笛卡尔积：返回行数为：前表符合条件的行乘上后表符合条件的行） 。形式上会灵活些.

使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入

。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中

的列集，后跟右输入返回的列的列表。

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),
)
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)
 

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
)
AS
BEGIN
  WITH Employees_Subtree(empid, empname, mgrid, lvl)--注释：此处用的是CTE公用表表达式
  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
 

若要返回每个部门经理的所有级别的全部下属，请使用下面的查询：

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

下面是结果集：

 复制代码
deptid      deptname   deptmgrid   empid       empname    mgrid       lvl
----------- ---------- ----------- ----------- ---------- ----------- ---
1           HR         2           2           Andrew     1           0
1           HR         2           5           Steven     2           1
1           HR         2           6           Michael    2           1
2           Marketing  7           7           Robert     3           0
2           Marketing  7           11          David      7           1
2           Marketing  7           12          Ron        7           1
2           Marketing  7           13          Dan        7           1
2           Marketing  7           14          James      11          2
3           Finance    8           8           Laura      3           0
4           R&D        9           9           Ann        3           0
5           Training   4           4           Margaret   1           0
5           Training   4           10          Ina        4           1
 

请注意，Departments 表中的每一行被复制的次数与 fn_getsubtree 返回给部门经理的行数一样多。

此外，园艺部门不会显示在结果中。因为此部门没有经理，fn_getsubtree 为其返回了一个空集。使用 OUTER APPLY，园艺部门也会

显示在结果集中，其中 deptmgrid 字段中的值以及 fn_getsubtree 返回的字段中的值均为 NULL。

e.g. 2

CREATE TABLE Arrays
(
  aid INT NOT NULL IDENTITY PRIMARY KEY,
  array VARCHAR(7999) NOT NULL
)
go
INSERT INTO Arrays VALUES('')
INSERT INTO Arrays VALUES('10')
INSERT INTO Arrays VALUES('20,40,30')
INSERT INTO Arrays VALUES('-1,-3,-5')
GO
CREATE FUNCTION  function1(@arr AS VARCHAR(7999))
  RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)
AS
BEGIN
  DECLARE @end AS INT, @start AS INT, @pos AS INT
  SELECT @arr = @arr + ',', @pos = 1,
    @start = 1, @end = CHARINDEX(',', @arr, @start)
  WHILE @end > 1
  BEGIN
    INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start))

    SELECT @pos = @pos + 1,
      @start = @end + 1, @end = CHARINDEX(',', @arr, @start)
  END
  RETURN
END
--test
select * from function1('200,400,300')
go

SELECT A.aid, F.*
FROM Arrays AS A
  CROSS APPLY function1(array) AS F
go
SELECT A.aid, F.*
FROM Arrays AS A
  OUTER APPLY function1(array) AS F
GO