create function f (no int)
returns varchar(4000)
as
begin
declare @ret varchar(4000)
set @ret=''
select @ret=@ret+str from t1
return @ret
end
returns varchar(4000)
as
begin
declare @ret varchar(4000)
set @ret=''
select @ret=@ret+str from t1
return @ret
end
returns varchar(8000)
as
begin
declare @tmp varchar(8000)
set @tmp=''
select @tmp=@tmp+str from t where no=@no
return @tmpend
goselect no, f(no) from t group by no
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+''+col from tb where no=@no
return(stuff(@s,1,1,' '))
endgo select no,dbo.f(no) from tb group by no
declare @t table(id int, no int, str varchar(10))
insert @t
select 1, 1, 'aa' union all
select 2, 2 , 'bb' union all
select 3, 1, 'cc'select no,sdf=case when count(*)=2 then min([str])+max([str]) else min([str]) end
from @t group by [no]当分组中子项最多有三个情况,都可以直接用一句SQL语句实现要求
超过三个,就要用函数了
create funcation fun ()
RETURNS @T TABLE(no int,f varchar(100))
as
insert into @T (select no, f(str) from t group by no)
return
select no, f(str) from t group by no这条查询语句还是不能实现阿,郁闷
returns varchar(4000)
as
begin
declare @ret varchar(4000)
set @ret=''
select @ret=@ret+str from t1
return @ret
end
没有问题!!
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+''+col from tb where no=@no
return(stuff(@s,1,1,' '))
endgo select no,dbo.f(no) from tb group by no
是可行的