22,300
社区成员




---创建字符连接函数---
create function [dbo].[f_str](@a varchar(20),@b varchar(20))
returns varchar(50)
as
begin
declare @s varchar(800)
select @s=isnull(@s+'_','')+ltrim(c)
from tb
where a=@a and b=@b
return @s
end
---创建字符连接函数---
create function [dbo].[f_str](@a varchar(20),@b varchar(20))
returns varchar(50)
as
begin
declare @s varchar(800)
select @s=isnull(@s+'_','')+ltrim(c)
from tb
where a=@a and b=@b order by tb.created desc
return @s
end
create table tb
(
id int,
[name] varchar(100),
created datetime
)
insert tb
select 1, 'a', '2010-01-01' union all
select 1, 'a', '2010-01-02' union all
select 3, 'c', '2010-01-03'
--function
alter function [dbo].[f_str](@a varchar(20),@b varchar(20))
returns varchar(50)
as
begin
declare @s varchar(800)
set @s = ''
select @s = @s + '_' + ltrim(convert(varchar(10), created, 120))
from tb
where id=@a and [name]=@b order by created desc
if @s > ''
set @s = right(@s, len(@s)-1)
return @s
end
--Result
select result = dbo.[f_str] (1, 'a')
/*
result
2010-01-02_2010-01-01
*/
--你的函数中:
select @s=isnull(@s+'_','')+ltrim(c)
from tb
--累计应该是类似这样:
select @s=@s+isnull(@s+'_','')+ltrim(c)
from tb
create table tb(id int,name nvarchar(10),created datetime)
insert into tb select 1,'a','2010-01-01'
insert into tb select 2,'b','2010-01-02'
insert into tb select 3,'c','2010-01-03'
go
declare @s nvarchar(500)
set @s=''
select @s=@s+name from tb order by created
select @s
go
drop table tb
/*
---------------------------------------------------------------------------------------
abc
(1 行受影响)
*/
declare @table table (id int,name varchar(1),created datetime)
insert into @table
select 1,'a','2010-01-01' union all
select 2,'b','2010-01-02' union all
select 3,'c','2010-01-03'
declare @s varchar(20)
set @s=''
select @s=@s+isnull('_'+name,'') from @table order by created desc
set @s=substring(@s,2,len(@s))
select @s
/*
c_b_a
*/
--这样就是累加了
declare @s varchar(800)
set @s=''
select @s=@s+isnull(@s+'_','')+ltrim(c)
from tb
where a=@a and b=@b order by tb.created desc
return @s
select @s=isnull(@s+'_','')+ltrim(c)
from tb
where a=@a and b=@b