--创建处理函数
create function f_str(@a1 int)
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+'/'+a2 from t1 where a1=@a1
return(stuff(@re,1,1,''))
end
go--调用实现查询
select a1,a3=dbo.f_str(a1)
from t1
group by a1
create function f_str(@a1 int)
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+'/'+a2 from t1 where a1=@a1
return(stuff(@re,1,1,''))
end
go--调用实现查询
select a1,a3=dbo.f_str(a1)
from t1
group by a1
create table t1(a1 int,a2 varchar(10))
insert t1 select 1,'one'
union all select 2,'two'
union all select 3,'three'
union all select 1,'one1'
union all select 2,'twot'
union all select 3,'threex'
union all select 3,'dog'
go--创建处理函数
create function f_str(@a1 int)
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+'/'+a2 from t1 where a1=@a1
return(stuff(@re,1,1,''))
end
go--调用实现查询
select a1,a3=dbo.f_str(a1)
from t1
group by a1
godrop table t1
drop function f_str/*--测试结果表明
a1 a3
----------- ---------------------
1 one/one1
2 two/twot
3 three/threex/dog(所影响的行数为 3 行)
--*/
return(stuff(@re,1,1,''))
最后这句的含义,为什么要stuff?
去掉多余的/
可以改为:
return(right(@re,len(@re)-1))
调两次函数,效率应该不如用stuff