我对表有一个简单的查询,它返回如下结果：
id id_type id_ref 2702 5 31 2702 16 14 2702 17 3 2702 40 1 2703 23 4 2703 23 5 2703 34 6 2704 1 14
我想将结果合并为一行,例如：
id concatenation 2702 5,16,17,40:31,14,3,1 2703 23,23,34:4,5,6 2704 1:14
在触发器中有没有办法做到这一点？
注意：我知道我可以使用光标,但我真的不愿意,除非没有更好的方法.
该数据库是Sybase 12.5.4版.
解决方法
由于使用select语句在Sybase中完成此操作相当困难,我建议使用while循环,如下所示.虽然循环优于游标,但速度要快得多.假设表名是MYTABLE：
CREATE TABLE #temp
(
aa numeric(5,0) identity,id int not null,id_type int not null,id_ref int not null
)
CREATE TABLE #results
(
id int not null,concatenation varchar(1000) not null,)
insert into #temp
select id,id_type,id_ref from MYTABLE order by id
declare @aa int,@maxaa int,@idOld int,@idNew int
declare @str1 varchar(1000),@str2 varchar(1000)
set @aa = 1
set @maxaa = (select max(aa) from #temp)
set @idNew = (select id from #temp where aa = 1),@idOld = @idNew
while @aa <= @maxaa
begin
set @idNew = (select id from #temp where aa = @aa)
IF @idNew = @idOld
BEGIN
set @str1 = @str1 + convert(varchar,(select id_type from #temp where aa = @aa)) + ',',@str2 = @str2 + convert(varchar,(select id_ref from #temp where aa = @aa)) + ','
IF @aa = @maxaa
insert into #results (id,concatenation)
VALUES (@idOld,left(@str1,len(@str1) - 1) + ':' + left(@str2,len(@str2) - 1) )
END
ELSE
BEGIN
insert into #results (id,len(@str2) - 1) )
set @str1 = NULL,@str2 = NULL
set @str1 = @str1 + convert(varchar,'
IF @aa = @maxaa
insert into #results (id,concatenation)
VALUES (@idNew,len(@str2) - 1) )
END
set @idOld = @idNew
set @aa = @aa+1
end
select * from #results
编辑
以下版本的速度提高了约45％
CREATE TABLE #temp
(
aa numeric(5,id_ref from MYTABLE order by id
declare @aa int,@str2 varchar(1000),@j int
set @aa = 1
set @maxaa = (select max(aa) from #temp)
set @idNew = (select id from #temp where aa = 1),@idOld = @idNew
set @str1 = ':'
while @aa <= @maxaa
begin
set @idNew = (select id from #temp where aa = @aa)
IF @idNew = @idOld
BEGIN
set @str2 = (select convert(varchar,id_type) + ':' + convert(varchar,id_ref) from #temp where aa = @aa)
set @j = (select charindex(':',@str2))
set @str1 = str_replace(@str1,':',substring(@str2,1,@j - 1) + ',:') + right(@str2,len(@str2) - @j) + ',left(str_replace(@str1,:',':'),len(@str1) - 2) )
END
ELSE
BEGIN
insert into #results (id,len(@str1) - 2) )
set @str1 = ':'
set @str2 = (select convert(varchar,len(@str1) - 2) )
END
set @idOld = @idNew
set @aa = @aa+1
end
select * from #results
