求一bom相关函数

求一bom相关函数--求一bom相关函数--bom表基本结构(父层id,子层id,半成品标志(1是，0非))create table #t(parent_id int,child_id int ,flag char(1))--求给出的一child_id，返回父层中第一个半成品标志为1的父层id,如本层是半成品标志为1返回本身，insert into #tselect 0,1,'0'union allselect 1,2,'1'union allselect 2,3,'0'union allselect 3,4,'1'union allselect 4,5,'0'---如上面的数据，如输入5>>或>>4>>，都返回3---如上面的数据，如输入3，返回3---如上面的数据，如输入2，返回1参考我的blog树形数据处理http://blog.csdn.net/zjcxc/archive/2003/12/29/20073.aspx--bom表基本结构(父层id,子层id,半成品标志(1是，0非))create table tb(parent_id int,child_id int ,flag char(1))--求给出的一child_id，返回父层中第一个半成品标志为1的父层id,如本层是半成品标志为1返回本身，insert into tbselect 0,1,'0' union allselect 1,2,'1' union allselect 2,3,'0' union allselect 3,4,'1' union allselect 4,5,'0'go--查询函数create function f_pid(@child_id int)returns intasbegindeclare @flag char(1)select top 1 @child_id=parent_id,@flag=flag from tb where @child_id in(child_id,parent_id)order by case when parent_id=@child_id then 0 else 1 endwhile @@rowcount>0 and @flag<>'1'select @child_id=case when flag='1' then parent_id else child_id end,@flag=flag from tb where child_id=@child_idreturn(case when @flag='1' then @child_id else null end)endgo---如上面的数据，如输入5>>或>>4>>，都返回3select dbo.f_pid(5),dbo.f_pid(4)---如上面的数据，如输入3，返回3select dbo.f_pid(3)---如上面的数据，如输入2，返回1select dbo.f_pid(2)go--删除测试drop table tbdrop function f_pid老大,帮忙看看，出不了结果!CREATE procedure p_1(@child_id numeric(18,0))asbegindeclare @id numeric(18,0) declare @flag char(1) declare @t table (parent_id numeric(18,0), child_id numeric(18,0) ,flag char(1))select @flag = flag from t where parent_id = @child_idif @flag= '1'return (@child_id)insert into @tselect parent_id,child_id,flag from t where parent_id = @child_idwhile @@rowcount>0begin if exists(select * from @t where @flag= '1')beginselect top 1 @id =parent_id from @t where @flag='1'breakendinsert into @tselect t.parent_id,t.child_id,t.flag from t join @t b on t.child_id = b.parent_id and t.parent_id not in (select parent_id from @t)endreturn(@id)EndGO