• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

一个SELECT语句引发的迭代(SELECT @local_variable = expression 的妙用)

李战 2006-04-03 01:20:45
一个SELECT语句引发的迭代(SELECT @local_variable = expression 的妙用)
作者:李战(leadzen)

  在用Transact-SQL编写程序过程中,经常会遇到针对某个查询的每一行记录进行某种迭代运算的操作。通常情况下,我们都会定义一个该查询的游标,然后用循环语句来取出每一行进行迭代处理。但对于某些计算性质的迭代,巧妙使用SELECT @local_variable = expression的形式却能取得更好的效果。

  我们来看看一个例子,有表T,只含一个字符字段F1,内容如下
T(F1)
------
A
B
C
D
E

  要求写一段Transact-SQL,返回'A,B,C,D,E'形式的结果,即将每一行记录用逗号串起来。这是一个典型的查询结果迭代操作。

  用游标来迭代可能的形式是:

declare @result varchar(8000)
declare @s varchar(32)
declare c cursor FORWARD_ONLY READ_ONLY for
select F1 from T order by F1
set @result = ''
open c
fetch next from c into @s
while @@fetch_status = 0
begin
set @result = case when @result='' then @s else @result + ',' + @s end
fetch next from c into @s
end
deallocate c
select @result

  但用SELECT @local_variable = expression的形式就可改成这样:

declare @result varchar(8000)
set @result = ''
select @result = @result + case when @result='' then F1 else ','+F1 end from T order by F1
select @result

  分析二者的执行计划发现,SELECT迭代法的执行计划比游标迭代法的执行计划简单多了,而且非常符合一般SELECT语句的执行计划。

  查看SQL Server的帮助文档是这样说的:

------------------------------------------------------------------------------
SELECT @local_variable

指定将(使用 DECLARE @local_variable 创建的)给定的局部变量设置为指定的表达式。

建议将 SET @local_variable 而不是 SELECT @local_variable 用于变量赋值。

......

注释

SELECT @local_variable 通常用于将单个值返回到变量中。例如,如果 expression 为列名,则返回多个值。如果 SELECT 语句返回多个值,则将返回的最后一个值赋给变量。

如果 SELECT 语句没有返回行,变量将保留当前值。如果 expression 是不返回值的标量子查询,则将变量设为 NULL。
......
-------------------------------------------------------------------------------

  注意其中的这句话“如果 SELECT 语句返回多个值,则将返回的最后一个值赋给变量”。
  虽然如此,但实际的执行结果的确都曾对每一行记录执行过@local_variable = expression,只是最后一行保留下来。

  而SELECT迭代的巧妙之处就是在expression表达式中又引用了@local_variable自身,很完美地将迭代计算融入SELECT语句内部的执行中!

  这样的迭代执行计划非常精简,又无需分配和释放游标,书写又精简,不能不说巧妙啊!

  感谢www.cndev.org的sea026和乌烟提供素材,以及各位大牛小牛的牛角尖啊...

李战(leadzen) 2006-3-24
...全文
99 点赞 收藏 3
写回复
3 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
撸大湿 2006-04-03
一般我们都 select @result = @result +","+ F1 from table where 条件
然后 stuff(..)
回复
zlp321002 2006-04-03
--这种方式很多人都知道.
回复
Guizhi 2006-04-03
mark
回复
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2006-04-03 01:20
社区公告
暂无公告