SQLServer 2005 和游标cursor说再见——apply运算符

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

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

--以上是SQLServer 2005帮助中的讲解,下面还是看个例子吧

-- apply运算符的主要用途就是和表值函数配合,用来替代SQLServer 2000中的游标

--Create Employees table and insert values

--员工表共四列员工id 部门主管id 员工姓名佣金

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

)

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 Departments table and insert values

--部门表共散列部门id 部门名称外键部门主管id

CREATE TABLE Departments

(

deptid INT NOT NULL PRIMARY KEY,

deptname VARCHAR(25) NOT NULL,

deptmgrid INT NULL REFERENCES Employees

)

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)

select * from employees

select * from Departments

结果:

SQLServer 2005 和游标cursor说再见——apply运算符

--表值函数根据部门主管id 查询出该部门主管下属员工

--with是CTE语法,不了解的先查询SQLServer 2005帮助

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

)

BEGIN

WITH Employees_Subtree(empid, empname, mgrid, lvl)

(

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

-- 根据上面的表值函数我们可以很方便的查询出某个部门主管的下属都是谁

--但是,如果查询出所有部门主管的下属就麻烦了,需要使用游标

--定义表变量临时存放数据

declare @tem table(

empid int,

mgrid int,

empname varchar(25),

lvl int,

deptid INT,

deptname VARCHAR(25),

deptmgrid INT

)

DECLARE @ids int

DECLARE test_cursor CURSOR FOR

select deptmgrid FROM Departments

OPEN test_cursor

FETCH NEXT FROM test_cursor

INTO @ids

WHILE @@FETCH_STATUS = 0

begin

insert into @tem select empid, mgrid, empname, lvl, deptid, deptname, deptmgrid from dbo.fn_getsubtree(@ids) left join Departments on deptmgrid=@ids

FETCH NEXT FROM test_cursor

INTO @ids

end

CLOSE test_cursor

DEALLOCATE test_cursor

select * from @tem

结果:

SQLServer 2005 和游标cursor说再见——apply运算符

--游标效率太差,万不得已不要使用,SQLServer2005的apply运算符可以替代它

SELECT *

FROM Departments AS D

CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST

SELECT *

FROM Departments AS D

OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST

结果:

SQLServer 2005 和游标cursor说再见——apply运算符

===================================================

最后一行数据体现出CROSS APPLY和OUTER APPLY的不同,这有点类似Inner join和left join的区别.

好吧,我承认我是标题党,apply配合表值函数的用法,只能替代只进游标,但是这种方法效率要比游标好的多.

SQLServer 2005 和游标cursor说再见——apply运算符

相关推荐