日期:2014-05-17 浏览次数:20536 次
--创建数据开始
if(object_id('father')is not null) drop table father
go
create table father
(
id int,
[name] varchar(50)
)
go
insert into father
select 1,'王爸爸' union
select 2,'张爸爸'
go
if(object_id('son')is not null) drop table son
go
create table son
(
[name] varchar(50),
father_id int
)
go
insert into son
select '大王子',1 union all
select '二王子',1 union all
select '三王子',1 union all
select '大张子',2 union all
select '二张子',2
go
--创建数据结束
--创建函数
create function getSonName()
returns @GetName table(fatherName varchar(50),sonName varchar(6000))
as
begin
declare @fatherID int
declare @fatherName varchar(50)
declare @SonName1 varchar(6000)
declare Cfather cursor for select id,[name] from father
open Cfather
fetch next from Cfather into @fatherID,@fatherName
while(@@fetch_status=0)
begin
set @sonName1=''
select @sonName1 = isnull(@sonName1+',',' ')+ [name] from son where father_id = @fatherid
insert into @Getname values(@fatherName,right(@sonName1,len(@sonName1)-1))
fetch next from Cfather into @fatherID,@fatherName
end
close Cfather
deallocate Cfather
return
end
go
--调用函数
select * from GetSonName()
--结果展示
/*
fatherName sonName
-------------------------------------------------- -------------------------------------------------------------------------------------------------------------------
王爸爸 大王子,二王子,三王子
张爸爸 大张子,二张子
(2 行受影响)
*/
drop table father