TABLE :student
stuid stuname sirid
1 tom 1
2 jack 1
3 ann 1
4 lucy 2
5 lily 2
我能不能得到第一个老师的所用学生名,以一个字符串的形式
names=tom,jack,amm,
就像sum的加和差不多东东
谢谢
stuid stuname sirid
1 tom 1
2 jack 1
3 ann 1
4 lucy 2
5 lily 2
我能不能得到第一个老师的所用学生名,以一个字符串的形式
names=tom,jack,amm,
就像sum的加和差不多东东
谢谢
insert into student select 1,'tom',1
union all select 2,'jack',1
union all select 3,'ann',1
union all select 4,'lucy',2
union all select 5,'lily',2declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+stuname from student where sirid=1
select names=stuff(@sql,1,1,'')drop table student
create function fn_Name(@sirid int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+rtrim(name)+',' from student where sirid=@sirid order by stuid
if @r<>''
set @r=left(@r,len(@r)-1)
return @r
end
go--调用
select Sirid,dbo.fn_Name(Sirid) as Name
from (
select distinct Sirid from student
) as a
create table student(stuid int,stuname varchar(10),sirid int)
insert into student select 1,'tom',1
union all select 2,'jack',1
union all select 3,'ann',1
union all select 4,'lucy',2
union all select 5,'lily',2
gocreate function dbo.fc_str(@sirid int)
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast(stuname as varchar(100)) from student where sirid=@sirid
return stuff(@sql,1,1,'')
end
goselect sirid,dbo.fc_str(sirid) as names from student group by siriddrop table student
drop function dbo.fc_str
create table t(stuid int identity(1,1), stuname varchar(10), sirid int)
insert t select 'tom',1
union all select 'jack',1
union all select 'ann',1
union all select 'lucy',2
union all select 'lily',2
GOcreate function f_str(@id int)
returns varchar(1000)
as
begin
declare @ret varchar(1000)
set @ret=''
select @ret=@ret+','+stuname from t where sirid=@id
set @ret=stuff(@ret,1,1,'')
return @ret
end
GO
select distinct sirid,stuname=dbo.f_str(sirid) from t
godrop table t
drop function f_str/*
sirid stuname
----------- ------------------------------
1 tom,jack,ann
2 lucy,lily
*/
returns varchar(100)
as
begin
declare @re varchar(100)
select @re=''
select @re=@re+','+ stuname from student where sirid=@id
return(stuff(@re,1,1,''))
end goselect sirid,学生=dbo.f(sirid) from student group by siridgo