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

　　在用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语句内部的执行中！

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

但要注意的是：查询优化器并不保证一定会遍历每一条记录！在某些情况下，查询优化器可能直接能定位到最后一条记录，这时，此方法将失效。

不过，经过我反复研究发现，如果给查询加上option(force order)的查询提示，查询都会按正确的顺序遍历全部记录，尚未发现列外情况。因此，此方法我已使用多年，还未出过错。

我想，虽然force order查询提示指示查询优化器强制按查询语句书写的顺序来连接关联的表，这里只有一个表，似乎与关联顺序无关。不过，force order 提示可能禁止了对最后一条记录的定位优化吧。

从哲学上讲，是没有办法证明一个理论是否正确，而只能采用所谓的“证伪”原则来判断。即，如果还没有发现违反某种理论的实验结果，那么该理论就是暂时是正确的。如果，某一天发现了违法该理论的实验结果，该理论即被推翻。

说不定那天微软就修改了查询优化器，从而推翻了我的理论。因此，请你在使用此方法的时候，一定要针对具体情况进行实地测试，否则代码出错不许骂我哟。　　

李战(leadzen).深圳 2006-3-24


－－－－－－－－－－－－－－－－－－－－－－－－－－
原blog（http://www.cnblogs.com/leadzen/archive/2008/02/12/1067469.html）不能评论，只好写在这里：

可惜这样不支持分组的迭代
如：
select ftype,flist+fname as flist
from T
group by ftype
（T有2个字段：ftype,fname）

不知道能否通过一个函数来实现，类似：
fun getlist(list,fname)
{
return list+fname
}
select ftype,getlist(flist,fname) as flist
from T
group by ftype

另外，sql块支持临时函数/过程就好了——现在的函数/过程只能先生成到数据块里，才能使用。。。。。。。