declare @tb table(code varchar(30), Kcode varchar(30))
insert into @tb select
'CB00013','CB00013-IR' union all select
'CB00013-IR','CB00157-IN' union all select
'CB00013-IR','ZHT0003' union all select
'CB00157-IN','ZAB0017' union all select
'CB00157-IN','ZBB0001' union all select
'CB00014','CB00014-IR' union all select
'CB00014-IR','CB00158-IN' union all select
'CB00014-IR','ZHT0003' union all select
'CB00158-IN','ZAB0017' union all select
'CB00158-IN','ZBB0001'
-- 测试
declare @tb2 table(code varchar(30), Kcode varchar(30) ,lel int)
declare @str varchar(30),@lel int
select @str='ZAB0017',@lel=0insert into @tb2 select code,kcode,@lel from @tb where kcode=@strwhile @@rowcount>0
begin
set @lel=@lel+1
insert into @tb2 select a.code,a.kcode,@lel from @tb a,@tb2 b where a.Kcode=b.Code and b.lel=@lel-1
endselect code from @tb2 where lel in(select max(lel) from @tb2)--结果
code
------------------------------
CB00013
CB00014(2 row(s) affected)
insert into @tb select
'CB00013','CB00013-IR' union all select
'CB00013-IR','CB00157-IN' union all select
'CB00013-IR','ZHT0003' union all select
'CB00157-IN','ZAB0017' union all select
'CB00157-IN','ZBB0001' union all select
'CB00014','CB00014-IR' union all select
'CB00014-IR','CB00158-IN' union all select
'CB00014-IR','ZHT0003' union all select
'CB00158-IN','ZAB0017' union all select
'CB00158-IN','ZBB0001'
-- 测试
declare @tb2 table(code varchar(30), Kcode varchar(30) ,lel int)
declare @str varchar(30),@lel int
select @str='ZAB0017',@lel=0insert into @tb2 select code,kcode,@lel from @tb where kcode=@strwhile @@rowcount>0
begin
set @lel=@lel+1
insert into @tb2 select a.code,a.kcode,@lel from @tb a,@tb2 b where a.Kcode=b.Code and b.lel=@lel-1
endselect code from @tb2 where lel in(select max(lel) from @tb2)--结果
code
------------------------------
CB00013
CB00014(2 row(s) affected)
create table tb (code varchar(30), Kcode varchar(30))
insert into tb select
'CB00013','CB00013-IR' union all select
'CB00013-IR','CB00157-IN' union all select
'CB00013-IR','ZHT0003' union all select
'CB00157-IN','ZAB0017' union all select
'CB00157-IN','ZBB0001' union all select
'CB00014','CB00014-IR' union all select
'CB00014-IR','CB00158-IN' union all select
'CB00014-IR','ZHT0003' union all select
'CB00158-IN','ZAB0017' union all select
'CB00158-IN','ZBB0001'
-- 测试
create function udf_getName(@productname varchar(30))
returns @product table (code varchar(30))
as
begin
declare @tb2 table(code varchar(30), Kcode varchar(30) ,lel int)
declare @lel int
select @lel=0insert into @tb2 select code,kcode,@lel from tb where kcode=@productnamewhile @@rowcount>0
begin
set @lel=@lel+1
insert into @tb2 select a.code,a.kcode,@lel from tb a,@tb2 b where a.Kcode=b.Code and b.lel=@lel-1
endinsert into @product select code from @tb2 where lel in(select max(lel) from @tb2)
return
end
select * from dbo.udf_getName('ZBB0001')--结果
code
------------------------------
CB00013
CB00014(2 row(s) affected)
你忽视了一点,不是所有的层次都是3层
有一些成品层次有4层、5层的。用insert into @product select code from @tb2 where lel in(select max(lel) from @tb2)只能得到层数最大的那些成品