日期:2014-05-18 浏览次数:20629 次
--bom结构,查找节点下所有子节点: 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