create function getid
@id int
as
while @id<>0
begin
insert resulttable select * from sourcetable where id=@id
set @id=select fid from sourcetable where id=@id
end;
@id int
as
while @id<>0
begin
insert resulttable select * from sourcetable where id=@id
set @id=select fid from sourcetable where id=@id
end;
returns @resulttable table
(
id int,
fid int,
name varchar(50)
)
as
begin
while @id<>0
begin
insert @resulttable select * from sourcetable where id=@id
select fid=@id from sourcetable where id=@id
end
return
end
/*
查询出来某个子类的所有父类,SQL Server版本的.
*/
declare @id int
declare @name varchar(50)
set @id = 13 --某个子类
while @id <> 0
begin
select @id = [id],@name = name from 你的表名 where [id]=@id
print @id
print @name
end
returns @resulttable table
(
id int,
fid int,
name varchar(50)
)
as
begin
while @id<>0
begin
insert @resulttable select * from sourcetable where id=@id
select fid=@id from sourcetable where id=@id
end
return
end