create table os(id int,parentid int,desn varchar(10))
insert into os select 1,0,'体育用品'
insert into os select 2,0,'户外运动'
insert into os select 3,1,'篮球'
insert into os select 4,1,'足球'
insert into os select 5,2,'帐篷'
insert into os select 6,2,'登山鞋'
insert into os select 7,0,'男士用品'
insert into os select 8,7,'刮胡刀'
insert into os select 9,3,'大号篮球'--求个节点下所有子节点:
create function f_cid(@id int)
returns varchar(500)
as
begin
declare @t table(id int,parentid int,desn varchar(10),lev int)
declare @lev int
set @lev=1
insert into @t select *,@lev from os where id=@id
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.*,@lev from os a,@t b
where a.parentid=b.id and b.lev=@lev-1
end
declare @cids varchar(500)
select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
return @cids
end
go--调用函数
select *,ids=dbo.f_cid(id) from os
--得到每个节点路径:
create proc wsp2
@id int
as
select *,cast(' ' as varchar(10)) fullpath into #os from os
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(parentid) from #os
update #os set fullpath=id
while @j<=@i
begin
update #os set fullpath=a.fullpath+','+ltrim(#os.id)
from #os inner join #os a on #os.parentid=a.id
where #os.parentid=@j
set @j=@j+1
end
select * from #os
go
--调用存储过程
exec wsp2 1
请高人们详细解释下这个函数和存储过程,
把你们的思路告诉给我吧~(详细点哦)谢谢咯
--2005有更簡單的方法
create table #os(id int,parentid int,desn varchar(10))
insert into #os select 1,0,'体育用品'
insert into #os select 2,0,'户外运动'
insert into #os select 3,1,'篮球'
insert into #os select 4,1,'足球'
insert into #os select 5,2,'帐篷'
insert into #os select 6,2,'登山鞋'
insert into #os select 7,0,'男士用品'
insert into #os select 8,7,'刮胡刀'
insert into #os select 9,3,'大号篮球'
go
with s(id)
as
(
select id from #os where parentid='1'
union all
select #os.id from #os inner join s on #os.parentid=s.id
)
select * from s
go
--求个节点下所有子节点:
create function f_cid(@id int)
returns varchar(500)
as
begin
declare @t table(id int,parentid int,desn varchar(10),lev int)
declare @lev int
set @lev=1
--先把自己插入到表中
insert into @t select *,@lev from os where id=@id
--如果有While里面的insert有插入記錄,那么繼續循環,@@rowcount的意思應該知道吧?上一條SQL影響的行數
while(@@rowcount>0)
begin set @lev=@lev+1
insert into @t select a.*,@lev from os a,@t b
where a.parentid=b.id and b.lev=@lev-1 end
declare @cids varchar(500)
select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
return @cids
end
go
declare @cids varchar(500)
select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
return @cids这里定义@cids是为什么?