coalesce的使用-白红宇

--------From Whale.cnblogs

有个数据表testC，数据为

col

--------------------------------------------------

现在有语句

declare @entry varchar(100)

select @entry=Null

select @entry=coalesce(@entry+';','') + col from testC

select @entry

结果：

--------------------------------------

b;c;d;e;f

(1 row(s) affected)

为什么结果会这样呢，

其实这里面已经用上了递归，其中

select @entry=coalesce(@entry+';','') + col from testC

就是递归.

每次调用coalesce()里面的@entry的时候，这个@entry变量就会再调用coalesce(@entry+';','') + c，每一个层次调用+c,的时候，都会导致记录集的指针下移。

先不看这个coalesce函数，简化一下

declare @entry varchar(100)

select @entry='a'

select @entry=@entry+ col from testC

select @entry

结果为：

----------------------------------------------------------------------------------------------------

abcdef

等同于

select @entry=(((a+b)+c)+d)+e)+f

递归总需要有一个最终层次的返回，那最里面的(a+b)就是递归的返回吗，显然不是，应该单独这个a就是最里面返回层的返回结果，也就是里面应该还有一层，那层是什么呢。是这记录集的最后最前面吧。

再看一下

declare @entry varchar(100)

declare @bb varchar(100)

select @entry='a'

select @bb=@entry+ col from testC

select @bb

结果是

也就是说，在没有递归的情况下，只返回和记录集最后一个相加的结果，而有递归时，会从后面往前递归，因为每次进行和字段相加，会导致指针的移动，是从后往前移的，到记录集的最前面的时候，递归就会返回。

create function dbo.addcol(@a varchar(10),@b varchar(10))

returns varchar(100)

begin

declare @entry varchar(100)

select @entry=null

select @entry=coalesce(@entry+';','')+c from testA where a= @a and b= @b

return @entry

end

select * from testA

select a, b, dbo.addcol(a,b) from testA group by a,b

drop function dbo.addcol

ID a B C

------ ---------- ---------- ----------

1 1 1 11

2 1 1 111

3 1 3 13

4 1 2 12

5 1 1 1111

(5 row(s) affected)

a b

---------- ---------- ----------------------------------------------------------------------------------------------------

1 1 11 ;111 ;1111

1 2 12

1 3 13

(3 row(s) affected)

--生成测试数据

create table 表(部门 int,人员 varchar(20))

insert into 表 select 1,'张三'

insert into 表 select 1,'李四'

insert into 表 select 1,'王五'

insert into 表 select 2,'赵六'

insert into 表 select 2,'邓七'

insert into 表 select 2,'刘八'

--创建用户定义函数

create function f_str(@department int)

returns varchar(8000)

begin

declare @ret varchar(8000)

set @ret = ''

select @ret = @ret+','+人员 from 表 where 部门 = @department

set @ret = stuff(@ret,1,1,'')

return @ret

end

--执行

select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门

--输出结果

部门人员

---- --------------

1 张三,李四,王五

2 赵六,邓七,刘八

--删除测试数据

drop function f_str

drop table 表

本文转自kenty博客园博客，原文链接http://www.cnblogs.com/kentyshang/archive/2006/09/02/492861.html如需转载请自行联系原作者

kenty