不用游标的字符串字段合并

[foolishchao]

参考下面的贴子
http://expert.csdn.net/Expert/topicview.asp?id=632928

修改后，解决原贴子的遗留问题。

set nocount on
if object_id('tempdb.dbo.#YourResultTable') is not null drop table #YourResultTable
create table #YourResultTable (id1 int,id2 varchar(5), value varchar(10))
insert #YourResultTable (id1,id2,value) values(8,'a','nice')
insert #YourResultTable (id1,id2,value) values(8,'a','niiice')
insert #YourResultTable (id1,id2,value) values(8,'a','ice')
insert #YourResultTable (id1,id2,value) values(1,'e','cool')
insert #YourResultTable (id1,id2,value) values(1,'e','nice')
insert #YourResultTable (id1,id2,value) values(1,'b','wow')
insert #YourResultTable (id1,id2,value) values(1,'b','wodw')
insert #YourResultTable (id1,id2,value) values(2,'a','cool')
insert #YourResultTable (id1,id2,value) values(2,'a','wow')
insert #YourResultTable (id1,id2,value) values(3,'c','cool')
insert #YourResultTable (id1,id2,value) values(3,'c','nice')
insert #YourResultTable (id1,id2,value) values(4,'a','nice')

select * from #YourResultTable

declare @z varchar(100),@id1 int,@id2 varchar(5)

if object_id('tempdb.dbo.#ProcessTable') is not null drop table #ProcessTable
create table #ProcessTable (id1 int,id2 varchar(5),value varchar(100), ProcessStep int identity(1,1))
insert #ProcessTable(id1,id2,value)
select id1,id2, value
from #YourResultTable
order by id1,id2,value

update #ProcessTable
set @z = value = case when @id1=id1 and @id2=id2 then @z+',' else '' end + value
,@id1 = id1
,@id2 = id2
from #ProcessTable

--select * from #ProcessTable

select id1,id2, value
from (select max(ProcessStep) MaxStep from #ProcessTable group by id1,id2) x
join #ProcessTable t
on ProcessStep = MaxStep

--在 MS SQL Server 2000 下通过。

[caiyunxia]

12W

@Z varchar(8000）已经不足了
是不是没有执行后面的记录？

[hooboo]

用j9988的测试生成数据

[Rewiah]

j9988：
发现没有，结果不一样

--1.
select * into #a from a order by 序号,内容
--select * from #a
declare @Z varchar(8000),@id int
set @id=1
set @z=''
update #a
set @z = 内容 = case when @id=序号 then 内容+ ','+ @z else 内容 end,@id=序号
from #a
select 序号,max(内容) 内容 into #t1 from #a group by 序号 order by 序号

drop table #a

--2.
--drop function getstr
create function getstrx(@ID INT)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+内容
from a
where 序号=@id
set @str=right(@str,len(@str)-1)
return @str
end

select 序号,dbo.getstrx(序号) 内容
into #t
from a x where id =( select max(id) from a where 序号=x.序号)



--检查
select * from #t a,#t1 b
where a.序号=b.序号
and a.内容<>b.内容

[j9988]

结果是一样的，但UPDATE那要改一下。
update #a
set @z = 内容 = case when @id=序号 then @z+ ','+内容 else 内容 end,@id=序号
from #a

[foolishchao]

to hooboo(任逍遥):
1）为什么要ID2，这个字段是否和字符串合并没有关系。
答：按主键进行字符串合并，飘香兄的语句的主键的一个字段，我写的语句是为了说明主键是多个字段也可以用飘香兄的方法的。

2）最后查询为什么用JOIN，这样其实效率很低，直接取MAX不就可以了吗？
答：直接取MAX是可以的。用JOIN的原因是：按飘香兄的语句改进，已将小表放在前面，应该比大表放在前面快。直接取MAX更好。

to happydreamer(小黑):
最后好像用
select id1,id2, max(value) from #ProcessTable group by id1,id2
order by id1,id2
也能得到结果
答：直接取MAX是可以的。用JOIN的原因是：按飘香兄的语句改进，已将小表放在前面，应该比大表放在前面快。直接取MAX更好。

[doudouniwan]

-----------------------------------------------------------------
set nocount on
declare @YourResultTable table (id char(1), value varchar(10))
insert @YourResultTable values('a', 'cool')
insert @YourResultTable values('a', 'nice')
insert @YourResultTable values('a', 'wow')
insert @YourResultTable values('a', 'cool')
insert @YourResultTable values('b', 'wow')
insert @YourResultTable values('c', 'cool')
insert @YourResultTable values('c', 'nice')
insert @YourResultTable values('d', 'nice')
select * from @YourResultTable


declare @z varchar(100),
@Step int,
@q int
select @Step = 0,
@q = 0

declare @ProcessTable table (id char(1), value varchar(100), ProcessStep int)
insert @ProcessTable
select id, value, 0
from @YourResultTable
order by id, value

update @ProcessTable
set @z = value = case @q when id then @z else '' end + value +
',' ,
@q = @q + case @q when id then 0 else 1 end,
@Step = ProcessStep = @Step + 1
from @ProcessTable

select id,
value
from @ProcessTable t
join (select max(ProcessStep) MaxStep
from @ProcessTable
group by id) x
on ProcessStep = MaxStep

set nocount off
-------------------------------------------------

[pengdali]

个人认为函数比较好！

[流星尔]

我也是用的自定义函数

[hooboo]

问foolishchao (亚超) 几个问题：
1）为什么要ID2，这个字段是否和字符串合并没有关系。
2）最后查询为什么用JOIN，这样其实效率很低，直接取MAX不就可以了吗？

谢谢！

[hooboo]

Rewiah(乘长风) ：

请注意这个查询最后使用了join ，相当于自连接。如果select * from table 复杂度是O（n)的话，这个语句的复杂度就是O(n^2)，所以说UPDATE的方法效率高的说法讲不通。

而自定义函数的复杂度也是O(n^2)，两者是一样的。并且函数的易用性和可读性远非这个UPDATE语句可比。所以UPDATE的做法并没有很多优点！只是高手们自己的偏爱而已！

如果哪位高手认为我说的不对，请反驳！谢谢！

[Rewiah]

hooboo(任逍遥):
有道理！
update 方法还有一个导到临时表的过程
我只是反对“有定论”的说法，我没作测试。
测试是最后说明问题的。

[happydreamer]

偶来温故一下

那个update的方法的确很妙,可以用来解决很多其他问题


foolishchao (亚超) 兄好久不见

最后好像用
select id1,id2, max(value) from #ProcessTable group by id1,id2
order by id1,id2
也能得到结果

[caiyunxia]

测试UPDATE与function
结果如下
create table a (序号 int, 内容 varchar(100))
insert into a (序号,内容) values(1,'aaaa')
insert into a (序号,内容) values(1,'bbbb')
insert into a (序号,内容) values(1,'cccc')
insert into a (序号,内容) values(2,'dddd')
insert into a (序号,内容) values(2,'dddd')
insert into a (序号,内容) values(2,'hhhh')
insert into a (序号,内容) values(3,'dddd')
insert into a (序号,内容) values(3,'dddd')
insert into a (序号,内容) values(3,'hhhh')
insert into a (序号,内容) values(4,'dddd')
insert into a (序号,内容) values(4,'dddd')
insert into a (序号,内容) values(4,'hhhh')
重复插入数据，功绩11520
使用UPDATE
select * into #a from a
declare @Z varchar(8000),@id int
set @id=1
set @z=''
update #a
set @z = 内容 = case when @id=序号 then 内容+ ','+ @z else 内容 end ,@id=序号
from #a
select 序号,max(内容) from #a group by 序号
drop table #a
用时18S
采用函数
create function getstr(@ID INT)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+内容
from TABLE
where content=@content
set @str=right(@str,len(@str)-1)
return @str

select distinct 序号 ,dbo.getstr(序号) 内容
from a

用时远远大于18S，共计5：04
UPDATE效率远远比函数高，跟踪发现，每一条记录，均产生一次调用函数

[caiyunxia]

用时远远大于18S，共计5：04
UPDATE效率远远比函数高，跟踪发现，每一条记录，均产生一次调用函数

[hooboo]

上述测试不具可比性，getstr函数可以用于另外一张表的，这样才是可以比。

[hooboo]

你把那个自连接去掉当然效率高了，我已经明白子连接为什么要使用，是为了应用于其他表的时候必须连接。

[caiyunxia]

更正测试结果
select * into #a from a order by 序号 （插入没有排序，没有完全处理）
declare @Z varchar(8000),@id int
set @id=1
set @z=''
update #a
set @z = 内容 = case when @id=序号 then 内容+ ','+ @z else 内容 end ,@id=序号
from #a
select 序号,max(内容) from #a group by 序号
drop table #a
用时46S
函数00：05：04
UPDATE效率远远比函数高，跟踪发现，每一条记录，均产生一次调用函数
认为用UPDATE好

[hooboo]

上述测试只是利用了UPDATE的长处来比Select的短处。显然上述是在一张表上操作，对其中一个字段修改，本身就是UPDATE，这时候显然用UPDATE方便。可是关联两张表呢？

[hooboo]

也就是说其实是UPDATE了＃a这个表。但实际上#a表中的这些结果并不一定就够用了，肯定要关联其他表。

[Rewiah]

caiyunxia(monkey) :
函数的调用方式可以优化:
select distinct 序号 ,dbo.getstr(序号) 内容
from a

-->

SELECT 序号 ,dbo.getstr(序号) 内容
FROM (
select distinct 序号 from a
) AS X

你再测试一下，这样同样环境有可比性。