--bom表基本结构(父层id,子层id,半成品标志(1是,0非)) create table tb(parent_id int,child_id int ,flag char(1)) --求给出的一child_id,返回父层中第一个半成品标志为1的父层id,如本层是半成品标志为1返回本身, insert into tb select 0,1,'0' union all select 1,2,'1' union all select 2,3,'0' union all select 3,4,'1' union all select 4,5,'0' go--查询函数 create function f_pid(@child_id int) returns int as begin declare @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 end while @@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_id return(case when @flag='1' then @child_id else null end) end go---如上面的数据,如输入5 或 4 ,都返回3 select dbo.f_pid(5),dbo.f_pid(4)---如上面的数据,如输入3,返回3 select dbo.f_pid(3)---如上面的数据,如输入2,返回1 select dbo.f_pid(2) go--删除测试 drop table tb drop function f_pid
老大,帮忙看看,出不了结果!CREATE procedure p_1(@child_id numeric(18,0))as begin declare @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_id if @flag= '1' return (@child_id) insert into @t select parent_id,child_id,flag from t where parent_id = @child_id while @@rowcount>0 begin
if exists(select * from @t where @flag= '1') begin select top 1 @id =parent_id from @t where @flag='1' break end insert into @t select 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)
create table tb(parent_id int,child_id int ,flag char(1))
--求给出的一child_id,返回父层中第一个半成品标志为1的父层id,如本层是半成品标志为1返回本身,
insert into tb
select 0,1,'0' union all
select 1,2,'1' union all
select 2,3,'0' union all
select 3,4,'1' union all
select 4,5,'0'
go--查询函数
create function f_pid(@child_id int)
returns int
as
begin
declare @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 end
while @@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_id
return(case when @flag='1' then @child_id else null end)
end
go---如上面的数据,如输入5 或 4 ,都返回3
select dbo.f_pid(5),dbo.f_pid(4)---如上面的数据,如输入3,返回3
select dbo.f_pid(3)---如上面的数据,如输入2,返回1
select dbo.f_pid(2)
go--删除测试
drop table tb
drop function f_pid
老大,帮忙看看,出不了结果!CREATE procedure p_1(@child_id numeric(18,0))as
begin
declare @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_id
if @flag= '1'
return (@child_id) insert into @t
select parent_id,child_id,flag
from t
where parent_id = @child_id
while @@rowcount>0
begin
if exists(select * from @t where @flag= '1')
begin
select top 1 @id =parent_id from @t where @flag='1'
break
end
insert into @t
select 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)
end return(@id)EndGO