SQL2005学习笔记 APPLY 运算符

使用APPLY运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。
表值函数作为右输入,外部表表达式作为左输入。
通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输出。
APPLY运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表。
APPLY 运算符的左操作数和右操作数都是表表达式。
这些操作数之间的主要区别是:右操作数可以使用表值函数,从左操作数获取一个列作为函数的参数之一。左操作数可以包括表值函数,但不能以来自右操作数的列作为参数。
演示一下APPLY 运算符的用法:


代码如下:

--建一个表
CREATE TABLE MyData
(
ids INT IDENTITY PRIMARY KEY,
Data NVARCHAR(1000)
)
go
--插入测试数据
INSERT INTO MyData VALUES('')
INSERT INTO MyData VALUES('a,b,c')
INSERT INTO MyData VALUES('q')
INSERT INTO MyData VALUES('i,p')
GO
select * from MyData
go
--查询结果
ids Data
1
2 a,b,c
3 q
4 i,p

建立一个表,作用是:按逗号分解字符,分解出的每一个字符做一行数据返回


代码如下:

create FUNCTION fun_MyData(
@data AS NVARCHAR(1000)
)
RETURNS @tem TABLE( id INT , value nvarchar(100) )
AS
BEGIN
select @data=isnull(@data,'')
if len(@data)=0
return --字符长度为0 ,退出
declare @id AS INT
select @id=1
declare @end AS INT
select @end = CHARINDEX(',', @data)
while(@end>0)
begin
insert into @tem(id,value)
select @id,left(@data,@end-1)
select @id=@id+1
select @data=right(@data,len(@data)-@end)
select @end = CHARINDEX(',', @data)
end
if len(@data)>0
begin
insert into @tem(id,value)
select @id,@data
end
RETURN
END

开始使用APPLY 运算符:


代码如下:

SELECT m.ids, f.*
FROM MyData m CROSS APPLY fun_MyData(data) f
go
--结果
ids id value
2 1 a
2 2 b
2 3 c
3 1 q
4 1 i
4 2 p
SELECT m.ids, f.*
FROM MyData m OUTER APPLY fun_MyData(data) f
go
--结果
ids id value
1 NULL NULL
2 1 a
2 2 b
2 3 c
3 1 q
4 1 i
4 2 p

我们看到OUTER APPLY返回的结果行比CROSS APPLY多。
这一点有点象inner join(内部联接)和Left Outer Join(左外部联接)之间的关系.
其实APPLY有两种形式:CROSS APPLY 和 OUTER APPLY。
CROSS APPLY仅返回外部表中通过表值函数生成结果集的行。
OUTER APPLY既返回生成结果集的行,也返回不生成结果集的行,其中表值函数生成的列中的值为 NULL。
以上是sql2005的解决方案,下面我演示一下sql2000怎么解决这样的查询:
思路是:做个循环来逐个链接查询。


代码如下:

--sql2000版本
declare @ids int
select @ids =0
declare @data nvarchar(200)
select @data=''
--定义表变量临时存放数据
declare @tem table(
ids int,
id int,
value nvarchar(100)
)
DECLARE test_cursor CURSOR FOR
SELECT ids, Data FROM MyData
OPEN test_cursor
FETCH NEXT FROM test_cursor
INTO @ids,@data
WHILE @@FETCH_STATUS = 0
begin
insert into @tem
select @ids,id,value
from dbo.fun_MyData(@data)
FETCH NEXT FROM test_cursor
INTO @ids,@data
end
CLOSE test_cursor
DEALLOCATE test_cursor
select * from @tem

同样得到了结果,但是sql2000要利用循环,这样代码复杂,计算耗时。
让我们充分利用Sql Server2005新兵器:APPLY运算符给我们带来的简便快捷的运算方式吧.

(0)

相关推荐

  • SQL2005学习笔记 APPLY 运算符

    使用APPLY运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数. 表值函数作为右输入,外部表表达式作为左输入. 通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输出. APPLY运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表. APPLY 运算符的左操作数和右操作数都是表表达式. 这些操作数之间的主要区别是:右操作数可以使用表值函数,从左操作数获取一个列作为函数的参数之一.左操作数可以包括表值函数,但不能以来自右操作数的列作为参数. 演示一下

  • SQL2005学习笔记 EXCEPT和INTERSECT运算符

    1.简介 EXCEPT和INTERSECT运算符使您可以比较两个或多个SELECT语句的结果并返回非重复值. 2.区别 EXCEPT运算符返回由EXCEPT运算符左侧的查询返回.而又不包含在右侧查询所返回的值中的所有非重复值. INTERSECT返回由INTERSECT运算符左侧和右侧的查询都返回的所有非重复值. 3.注意事项 (1).使用EXCEPT或INTERSECT比较的结果集必须具有相同的结构.它们的列数必须相同,并且相应的结果集列的数据类型必须兼容 (2).INTERSECT运算符优先

  • Lua学习笔记之运算符和表达式

    本篇博客学习一下Lua的运算符,比较简单,我将说明直接写到了代码中,代码如下. --算术运算符 --二元运算符:+ - * / ^ (加减乘除幂) --一元运算符:- (负值) --这些运算符的操作数都是实数,Lua中没有自增自减的运算符. --关系运算符 -- < > <= >= == ~= --这里需要注意的是不等于是用~=表示的 --这些操作符返回结果为false或者true:==和~=比较两个值,如果两个值类型不同,Lua认为两者不同: --nil只和自己相等.Lua通过引

  • SQL2005 学习笔记 窗口函数(OVER)

    1.简介: SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合,通过它可以非常方便地累计总数.移动平均值.以及执行其它计算. 窗口函数功能非常强大,使用起来也十分容易.可以使用这个技巧立即得到大量统计值. 窗口是用户指定的一组行. 开窗函数计算从窗口派生的结果集中各行的值. 2.适用范围: 排名开窗函数和聚合开窗函数. 也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用 OVER子句前面必须是排名函数或者是聚合函数 3.例题: 复制代码 代码如下: --建立订单表 c

  • SQL2005 学习笔记 公用表表达式(CTE)

    公用表表达式 (CTE) 可以认为是在单个 SELECT.INSERT.UPDATE.DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集. CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效. 与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次. CTE可用于: 1.创建递归查询(我个人认为CTE最好用的地方) 2.在同一语句中多次引用生成的表 CTE优点: 使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点. 查询可以分为单独块.简

  • Javascript学习笔记 delete运算符

    一.语法 delete后面的表达式必须给出一个属性的引用,比如 var o = {a:1}; delete o.a; //此处o.a是对象o的属性a的引用 只有在with语句里才能使用单独的属性名 复制代码 代码如下: with(o){ delete a; } 二.delete的返回值 delete是普通运算符,会返回true或false.规则为:当被delete的对象的属性存在并且不能被删除时 返回false,否则返回true. 这里的一个特点就是,对象属性不存在时也返回true,所以返回值并

  • Python学习笔记之列表和成员运算符及列表相关方法详解

    本文实例讲述了Python学习笔记之列表和成员运算符及列表相关方法.分享给大家供大家参考,具体如下: 列表和成员运算符 列表可以包含我们到目前为止所学的任何数据类型并且可以混合到一起. lst_of_random_things = [1, 3.4, 'a string', True] # 这是一个包含 4 个不同类型元素的列表 print(lst_of_random_things[0]) # 1 获取上述列表的第一个值和最后一个值 print(lst_of_random_things[0]) #

  • JavaScript学习笔记之取数组中最大值和最小值

    推荐阅读:JavaScript学习笔记之数组的增.删.改.查 JavaScript学习笔记之数组求和方法 JavaScript学习笔记之数组随机排序 在实际业务中有的时候要取出数组中的最大值或最小值.但在数组中并没有提供arr.max()和arr.min()这样的方法.那么是不是可以通过别的方式实现类似这样的方法呢?那么今天我们就来整理取出数组中最大值和最小值的一些方法. 取数组中最大值 可以先把思路理一下: 将数组中第一个元素赋值给一个变量,并且把这个变量作为最大值: 开始遍历数组,从第二个元

  • python 匿名函数与三元运算学习笔记

    匿名函数 匿名函数就是不需要显示式的指定函数名 首先看一行代码: def calc(x,y):     return x*y print(calc(2,3))   # 换成匿名函数   calc = lambda x,y:x*y print(calc(2,3)) 你也许会说,用上这个东西没感觉有毛方便呀, ....呵呵,如果是这么用,确实没毛线改进,不过匿名函数主要是和其它函数搭配使用的呢,如下 res = map(lambda x:x**2,[1,2,3,4,5]) print(list(re

  • JavaWeb学习笔记分享(必看篇)

    自定义列表 <dl></dl>:表示列表的范围 **在里面 <dt></dt>:上层内容 **在里面 <dd></dd>:下层内容 有序列表 <ol></ol>:有序列表的范围 --属性 type:设置排序方式,1(默认),a,i.. **在ol标签里面 <li>具体内容</li> 无序列表 <ul></ul>:无序列表的范围 --属性 type:circle(空

随机推荐