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

系统 2116 0

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

)

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

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

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)

Go

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

)

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

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

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

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

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

结果 :

-- 游标效率太差 , 万不得已不要使用 ,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运算符


更多文章、技术交流、商务合作、联系博主

微信扫码或搜索:z360901061

微信扫一扫加我为好友

QQ号联系: 360901061

您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧,狠狠点击下面给点支持吧,站长非常感激您!手机微信长按不能支付解决办法:请将微信支付二维码保存到相册,切换到微信,然后点击微信右上角扫一扫功能,选择支付二维码完成支付。

【本文对您有帮助就好】

您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请用微信扫描上面二维码支持博主2元、5元、10元、自定义金额等您想捐的金额吧,站长会非常 感谢您的哦!!!

发表我的评论
最新评论 总共0条评论