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

...全文