create table tb(Kind int,Content varchar(5)) insert into tb select 1,'a' union all select 1,'b' union all select 2,'c' union all select 3,'d' union all select 3,'e' gocreate function usf_GetContent(@Kind int) returns varchar(50) as begin declare @ret varchar(50) set @ret='' select @ret=@ret+Content+',' from tb where Kind=@Kind set @ret=left(@ret,len(@ret)-1) return @ret end goselect Kind,Content=dbo.usf_GetContent(Kind) from tbdrop table tb drop function usf_GetContentKind Content ----------- -------------------------------------------------- 1 a,b 1 a,b 2 c 3 d,e 3 d,e(所影响的行数为 5 行)
修正:应该加上group by select Kind,Content=dbo.usf_GetContent(Kind) from tb group by KindKind Content ----------- -------------------------------------------------- 1 a,b 2 c 3 d,e(所影响的行数为 3 行)
insert into tb
select 1,'a' union all
select 1,'b' union all
select 2,'c' union all
select 3,'d' union all
select 3,'e'
gocreate function usf_GetContent(@Kind int)
returns varchar(50)
as
begin
declare @ret varchar(50)
set @ret=''
select @ret=@ret+Content+','
from tb
where Kind=@Kind
set @ret=left(@ret,len(@ret)-1)
return @ret
end
goselect Kind,Content=dbo.usf_GetContent(Kind)
from tbdrop table tb
drop function usf_GetContentKind Content
----------- --------------------------------------------------
1 a,b
1 a,b
2 c
3 d,e
3 d,e(所影响的行数为 5 行)
select Kind,Content=dbo.usf_GetContent(Kind)
from tb
group by KindKind Content
----------- --------------------------------------------------
1 a,b
2 c
3 d,e(所影响的行数为 3 行)
你用 char(10) 来测试