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

foolishchao 2003-05-16 06:40:16
参考下面的贴子
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 下通过。
...全文
19 点赞 收藏 44
写回复
44 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Rewiah 2003-05-17
caiyunxia(monkey) :
函数的调用方式可以优化:
select distinct 序号 ,dbo.getstr(序号) 内容
from a

-->

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

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

回复
hooboo 2003-05-17
也就是说其实是UPDATE了#a这个表。但实际上#a表中的这些结果并不一定就够用了,肯定要关联其他表。

回复
hooboo 2003-05-17
上述测试只是利用了UPDATE的长处来比Select的短处。显然上述是在一张表上操作,对其中一个字段修改,本身就是UPDATE,这时候显然用UPDATE方便。可是关联两张表呢?
回复
caiyunxia 2003-05-17
更正测试结果
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 2003-05-17
你把那个自连接去掉当然效率高了,我已经明白子连接为什么要使用,是为了应用于其他表的时候必须连接。

回复
hooboo 2003-05-17
上述测试不具可比性,getstr函数可以用于另外一张表的,这样才是可以比。
回复
caiyunxia 2003-05-17

用时远远大于18S,共计5:04
UPDATE效率远远比函数高,跟踪发现,每一条记录,均产生一次调用函数
回复
caiyunxia 2003-05-17
测试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效率远远比函数高,跟踪发现,每一条记录,均产生一次调用函数



回复
happydreamer 2003-05-17
偶来温故一下

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


foolishchao (亚超) 兄好久不见

最后好像用
select id1,id2, max(value) from #ProcessTable group by id1,id2
order by id1,id2
也能得到结果
回复
Rewiah 2003-05-17
hooboo(任逍遥):
有道理!
update 方法还有一个导到临时表的过程
我只是反对“有定论”的说法,我没作测试。
测试是最后说明问题的。
回复
hooboo 2003-05-17
Rewiah(乘长风) :

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

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

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


回复
hooboo 2003-05-17
问foolishchao (亚超) 几个问题:
1)为什么要ID2,这个字段是否和字符串合并没有关系。
2)最后查询为什么用JOIN,这样其实效率很低,直接取MAX不就可以了吗?

谢谢!
回复
流星尔 2003-05-17
我也是用的自定义函数
回复
pengdali 2003-05-17
个人认为函数比较好!
回复
doudouniwan 2003-05-17
-----------------------------------------------------------------
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
-------------------------------------------------

回复
foolishchao 2003-05-17
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更好。

回复
j9988 2003-05-17
结果是一样的,但UPDATE那要改一下。
update #a
set @z = 内容 = case when @id=序号 then @z+ ','+内容 else 内容 end,@id=序号
from #a
回复
Rewiah 2003-05-17
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.内容

回复
hooboo 2003-05-17
用j9988的测试生成数据
回复
caiyunxia 2003-05-17
12W

@Z varchar(8000)已经不足了
是不是没有执行后面的记录?
回复
加载更多回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2003-05-16 06:40
社区公告
暂无公告